How do I download a csv into a vba array?

Question

I want to import data into MS Access 2010 tables, but the data I import will depend on the csv file I download. As such, I need to first import the CSV file from a URL into an VBA container of some sort.

This post seems to discuss the loading of CSV files into vba arrays, but not from a URL...

So far I've done this to download the CSV file:

Public Function GetFileFromURL(ByVal url As String) As Object

Dim myURL As String
myURL = "http://www.somesite.com/file.csv"

Set GetFileFromURL = CreateObject("Microsoft.XMLHTTP")
GetFileFromURL.Open "GET", myURL, False
GetFileFromURL.Send

End Function

Sub DownloadSomething()

GetFileFromURL("ichart.yahoo.com/table.csv?s=GOOG&a=0&b=1&c=2000&d=0&e=31&f=2010&g=d&ignore=.csv")

End Sub

This runs, but I have no idea what to do with that GetFileFromURL object; At this point it's presumably just a list of strings, but how do I even get at those, and when I do, how do I convert them to arrays?

I would consider just downloading the file in a directory and reading it from there. If the script just returns a bunch of strings, then it will be hard to process. Of course you can read it into an array and parse it. The question is if this will not bring you in trouble once the csv changes its inherent format. — html_programmer, Oct 22 '13 at 07:33

score 1 · Answer 1 · answered Oct 29 '13 at 03:27

I don't know if this would help you. This is a routine I used for a quality control program. The csv file had 4 rows with the max 20 lines, hence arrData(80) but It can be less than 20 lines also. I used this to take dimensions off an autocad drawing (which never was more than 20) and fill in the text boxes.

Kim is correct, it must be the same form of csv format each time.

Private Sub Import_Click()
   On Error GoTo HandleError
    RoutineName = Form.Name & " Import_Click Event"

    Dim Detline As String        'Record form selected file
    Dim I As Integer             'Index for rows (table & array)
    Dim FF As Integer            'next file number available for use by the Open statement
    Dim dlgOpen As FileDialog

    Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
    With dlgOpen
        With .Filters
            .Clear
            .Add "Import csv or txt", "*.csv; *.txt", 1
            .Add "All Files", "*.*", 2
    End With
     .InitialFileName = "c:\QControl\"
    .FilterIndex = 1
    .AllowMultiSelect = False
    .Title = "Select document to import"
    .ButtonName = "Ok"
    .Show
    If .SelectedItems.Count > 0 Then
        SelectedFile = .SelectedItems(1)
    End If

End With

ReDim arrData(80)
DoCmd.GoToRecord acDataForm, "Main QC Form", acNewRec
I = 1                        ' initalize counter
FF = FreeFile                'Use FreeFile to supply a file number that is not already in use.
Open SelectedFile For Input As #FF
Do While Not EOF(FF)         'Read selected file line by line
  Line Input #FF, Detline
  arrData = Split(Detline, ",")
  Me.Controls("letter" & I) = arrData(0)
  Me.Controls("printdim" & I) = arrData(1)
  Me.Controls("stepnumber" & I) = arrData(2)
  Me.Controls("Gage" & I) = arrData(3)
  I = I + 1   'increment counter
Loop

RunCommand acCmdSaveRecord ' Save the Record you're looking at
Me.Requery ' Force the Recordset of the Form to update
Me.Form1.Requery

Close #FF   'close text file
DoCmd.GoToRecord , , acLast

Exit_Import_Click:
    DoCmd.SetWarnings True
    Exit Sub
HandleError:
    RtnCode = ErrorHandler   (EH_NumError:=Err.Number,DescError:=
   Err.Description,NameRoutine:=RoutineName)

   Resume Exit_Import_Click
 End Sub

How do I download a csv into a vba array?

1 Answers1