Importing CSV as text

[Guest]

I'm trying to set up a script to import a CSV with every column formatted as
text to avoid loss of leading zeros that may be part of an actual barcode or
asset ID. The CSV file itself comprises of close to 100 columns. I would also
like to be able to ask the user for the file name and path as part of the
import.

I've tried several test scenarios using small CSV files and recording the
action, but I've been having trouble calling the file name.

Here is what I get when recording:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\userID\Desktop\test.csv",
Destination:= _
Range("A1"))
.Name = "test"
.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
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

I would like to be able to replace the "C:\Documents and
Settings\userID\Desktop\test.csv" with the user's selected file and path.

 

[eseitomer]

Rudy,
Try this:

Dim constr As String
Dim fd As FileDialog
Dim Ifile As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.AllowMultiSelect = False
.InitialView = msoFileDialogViewDetails
.Filters.Add "Text", "*.txt; *.rtf", 1
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Ifile = vrtSelectedItem
Next vrtSelectedItem
Else
Ifile = "Cancel"
End If
End With
Set fd = Nothing

If Ifile <> "Cancel" Then
constr = "TEXT;" & Ifile

With ActiveSheet.QueryTables.Add(Connection:= _
constr,
Destination:= _
Range("A1"))
.Name = "test"
.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
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End If


Just copy and paste that instead of the current code in the macro sub.
It worked with my test case. Let me know how it turns out.

-E

 

[Mika Oukka]

This is one way to call the file

Answr = InputBox("Feed the file name", FName)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\userID\Desktop\" & FName & ".csv",
Destination:=Range("A1"))
....

You could also have the user to feed the filename to a cell in a worksheet.
To get the value from there use this type of code:

Fname = Sheets("Sheet1").Range("B2").Value

Mika Oukka