Populate Array using .csv File

Question

Office 2003, Windows XP

I've been using a process to populate an array behind a VBA UserForm from an SQL data source. Now the client wants to provide 'offline' access to the data to some users through an extract from the SQL database in .csv format. I've done something similar with named ranges in an Excel file using (rather messy "for-dev-purposes-only") code that looks like this:

Private Sub LoadExcelDataArrays()

Dim myPath As String

Dim xlApp As Excel.Application

Dim myWorkBook As Excel.Workbook

Dim bStartApp As Boolean

    On Error Resume Next

    myPath = myDoc.AttachedTemplate.Path & Application.PathSeparator

    Set xlApp = GetObject(, "Excel.Application")

    If Err Then

        bStartApp = True

        Set xlApp = New Excel.Application

    End If

    On Error GoTo OOPS

    With xlApp

        Set myWorkBook = .Workbooks.Open(myPath & "POC.xls")

        ProductsArray = myWorkBook.Names("ProductDetails").RefersToRange.Value

        AccessoriesArray = myWorkBook.Names("Accessories").RefersToRange.Value

        myWorkBook.Close SaveChanges:=False

        Set myWorkBook = Nothing

    End With

    If bStartApp = True Then xlApp.Quit

    Set xlApp = Nothing

Exit Sub

OOPS:

    MsgBox "FATAL ERROR LOADING DATA FROM DATA SOURCE. CONTACT HELP DESK."

    myDoc.Close wdDoNotSaveChanges

    End

End Sub

This code simply opens a Workbook and populates a couple of arrays using the values contained in two named ranges, but of course named ranges are not available for use in .csv files. (Note, too, that there will be two separate .csv files in place of the two named ranges I'm using currently, and I'm well aware that I'll need to open each one individually.)

The .csv files currently do not have a header row, although I could arrange to have one included if necessary. Otherwise, they are really quite simple. One has 4 columns: Product ID, Product Name, Standard Specifications and Marketing contents in the first; and Product ID and Accessory in the other. (As may be apparent, 'Product ID' is the key field that links items from one file to items in the other, but this is all handled in the VBA and works just fine.) I just need some direction on the VBA required to do the work that .RefersToRange.Value does in delimiting the data.

Cheers!

Gordon

(And sorry about the stupid User ID but I've tried to change it 3 times now with no joy...)

Answer 1

Hiding under a pseudonym now?

Open the csv file with Excel and use

Set myarray = Range("A1").CurrentRegion.Value

-- Hope this helps.

Doug Robbins - Word MVP,

dkr[atsymbol]mvps[dot]org

Posted via the Community Bridge

"VBA Developer" wrote in message news:*** Email address is removed for privacy ***...

Office 2003, Windows XP

I've been using a process to populate an array behind a VBA UserForm from an SQL data source. Now the client wants to provide 'offline' access to the data to some users through an extract from the SQL database in .csv format. I've done something similar with named ranges in an Excel file using (rather messy "for-dev-purposes-only") code that looks like this:

Private Sub LoadExcelDataArrays()

Dim myPath As String

Dim xlApp As Excel.Application

Dim myWorkBook As Excel.Workbook

Dim bStartApp As Boolean

    On Error Resume Next

    myPath = myDoc.AttachedTemplate.Path & Application.PathSeparator

    Set xlApp = GetObject(, "Excel.Application")

    If Err Then

        bStartApp = True

        Set xlApp = New Excel.Application

    End If

    On Error GoTo OOPS

    With xlApp

        Set myWorkBook = .Workbooks.Open(myPath & "POC.xls")

        ProductsArray = myWorkBook.Names("ProductDetails").RefersToRange.Value

        AccessoriesArray = myWorkBook.Names("Accessories").RefersToRange.Value

        myWorkBook.Close SaveChanges:=False

        Set myWorkBook = Nothing

    End With

    If bStartApp = True Then xlApp.Quit

    Set xlApp = Nothing

Exit Sub

OOPS:

    MsgBox "FATAL ERROR LOADING DATA FROM DATA SOURCE. CONTACT HELP DESK."

    myDoc.Close wdDoNotSaveChanges

    End

End Sub

This code simply opens a Workbook and populates a couple of arrays using the values contained in two named ranges, but of course named ranges are not available for use in .csv files. (Note, too, that there will be two separate .csv files in place of the two named ranges I'm using currently, and I'm well aware that I'll need to open each one individually.)

The .csv files currently do not have a header row, although I could arrange to have one included if necessary. Otherwise, they are really quite simple. One has 4 columns: Product ID, Product Name, Standard Specifications and Marketing contents in the first; and Product ID and Accessory in the other. (As may be apparent, 'Product ID' is the key field that links items from one file to items in the other, but this is all handled in the VBA and works just fine.) I just need some direction on the VBA required to do the work that .RefersToRange.Value does in delimiting the data.

Cheers!

Gordon

(And sorry about the stupid User ID but I've tried to change it 3 times now with no joy...)

---

Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org

Answer 2

After a bit of investigation, it appears that Doug's answer does work quite nicely. Thanks again Doug!

Cheers!

Gordon

---

Gordon Bentley-Mix

Answer 3

<g> Yeah, something like that. Can't seem to get my ID changed. Every time I try it just reverts back to this stupid one. ~shrug~

Sorry to have cross-posted this here and motivating you to provide the same response twice, but with the demise of the old Word programming newsgroup, I'm not really sure where to go to find an answer. I've posted a reply to your response in the MSDN forum and am happy to continue corresponding with you there. However, I'll leave this thread active as well just in case someone who doesn't use the MSDN forum spots it and has something useful to offer.

Cheers!

Gordon

---

Gordon Bentley-Mix