Importing CSV in Excel, choosing file

May 15, 2009 at 04:33:08
Specs: Windows XP
Hello,

I have 'written' the following Excel macro. I have a .csv file that I want to import data from. The data within the .csv will change from time-to-time, but the format of this data will stay the same.

I used the macro record function to do this:
Data > Import External Data > Import Data. This works well. But it only defines the file for one location. This could change, if for example the spreadsheet is run on another computer and the user stores the .csv in another location.

I want Excel to prompt the user to select the .csv file, before running these same recorded instructions.

Could someone please help me implement this?

Here's the macro code:

Sub modelled_import()
'
' modelled_import Macro
' Macro recorded 14.05.2009 by James Weber
'

'
Range("A2:AZ500").Select
Selection.ClearContents
Range("A3").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\Documents and Settings\webejam\My Documents\Documents\Unified Spreadsheets\James\EngCase_HydBHIAgainstMD_20090507113924.csv" _
, Destination:=Range("A2"))
.Name = "EngCase_HydBHIAgainstMD_20090507113924_7"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.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
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub



See More: Importing CSV in Excel, choosing file

Report •


#1
May 15, 2009 at 07:47:15
Take a look at the GetOpenFilename method in VBA help.

Then modify the following line something like this:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fileToOpen, Destination:=Range("A2"))

P.S. As you may know, the macro recorder records everything it thinks it might possibly need, such as every property associated with a query. Most times a lot of the extraneous stuff can be deleted.

For example, doing a query on txt files (since I don't have any csv files to work on) I was able to reduce your code to the following.

You might want to take a look at the properties that will remain as defaults and eliminate them from the code just to make it easier to follow.

Sub modelled_import_v2()
'
' modelled_import Macro
' Macro modified 15.05.2009 by DerbyDad03

fileToOpen = Application _
    .GetOpenFilename("Text Files (*.txt), *.txt")

Range("A2:AZ500").ClearContents

  With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
        & fileToOpen, Destination:=Range("A2"))
   .RefreshStyle = xlInsertDeleteCells
   .Refresh BackgroundQuery:=False
  End With
End Sub


Report •

#2
May 18, 2009 at 06:13:01
Thanks a lot!

That's a much more efficient way of accessing the file. I managed to get rid of most of the recorded code. It works well now.

Great post.


Report •

Related Solutions


Ask Question