Importing a .csv file

[dmacmillan]

Hi All,

For the purposes of refreshing the data On Open and OnTime is the data import wizard preferred method over VBA or vice-versa? Does it matter?

Thank you in advance,
David

 

[Joe4]

I am not sure I really understand the question. If you are using OnTime or Workbook_Open, you are using VBA. How would you manually use the Data Import Wizard in an automated setting?

By the way, if you record yourself using the Data Import Wizard manually, you will get the VBA code you need to do the import.

 

[dmacmillan]

Hi,

Thank you for responding and apologies for not making myself clear.

I guess the nub of question is, are there any downsides to conducting the refresh from VBA? Is it necessary to delete the connection On Close? If this is not done will connections be continuously added; does this matter?

Welcome your thoughts. Appreciate your consideration.

Kind regards,
David

 

[Joe4]

Its hard to say without seeing your code to know what it is actually doing and how it is connecting.

 

[dmacmillan]

Thanks for coming back ...

I will use this form of code:

' filename = CSV filename without directory (test.csv)
' outSheet = name of the worksheet in the current workbook
' where the data should go, will start in A1

Function doFileQuery(filename As String, outSheet As String) As Boolean
    Dim rootDir As String
    rootDir = "C:\myDirectory"
    Dim connectionName As String
    connectionName = "TEXT;" + rootDir + "\" + filename
    With Worksheets(outSheet).QueryTables.Add(Connection:=connectionName, Destination:=Worksheets(outSheet).Range("A1"))
        .Name = filename
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh BackgroundQuery:=False
    End With
End Function

Kind regards,
David

 

[Joe4]

To be honest, I really don't know much about exactly how it all connects and whether connections should be closed. I have used the methodology you have mentioned, and I do sometimes find the connection messages quite annoying. Importing the data directly doesn't seem to have the same issues, so that is what I usually prefer (all things being equal).

Note that you can automate a data import as well, just the Macro Recorder and record yourself doing so to get the "bones" of that code.

 

[dmacmillan]

Joe,

Thank you for your response; food for thought that adds to the body of knowledge.

Kind regards,
David