Macro to import delimited file

[MJV8198]

I am trying to open a delimited file while in my original workbook so I can paste the data into my existing sheet. I have the following macro which allows me to find the file but it does not go through the import wizard.

Sub Import_Data()
Dim strFileToOpen As String

strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="All Files *.* (*.*),")
If strFileToOpen = "False" Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open Filename:=strFileToOpen
End If
End Sub

How do I add the wizard details to the macro?

here is a sample of the data.
ItemNo|Desc|LastCost|Price1|QtySold|SalesAmt|GrossPftAmt|QtyOnHand|QtyOnOrd|QtySoldYtd|SalesAmtYtd|GrossPftAmtYtd|MinOrdQty|ItemCat|ItemSubCat|Class|SubClass|Group|SubGroup
"ICCC1425"|"CC LETS GO CAMPING"|10.67|24.99|0|0|0|0|0|1|30|20|6|"13"|"529"|" "|"NSO"|" "|" "
"ICCC1444"|"CC ADVENTURE TREE HOUSE"|33.95|69.99|0|0|0|3|0|0|0|0|3|"13"|"529"|" "|" "|" "|"D"
"ICCC1445"|"BABY DISCOVERY FOREST"|21.34|44.99|0|0|0|1|0|1|45|24|4|"13"|"529"|" "|" "|" "|" "
"ICCC1447"|"CC ICE CREAM TRUCK"|16.97|34.99|0|0|0|5|0|1-|35-|18-|4|"13"|"529"|" "|"NSO"|" "|" "
"ICCC1448"|"CC BABY BATHTIME RTP"|12.12|24.99|0|0|0|4|0|0|0|0|6|"13"|"529"|" "|" "|" "|" "
"ICCC1449"|"CHIHUAHUA FAMILY"|12.12|24.99|0|0|0|6|0|0|0|0|6|"13"|"529"|" "|"NSO"|" "|" "
"ICCC1450"|"CHIHUAHUA TWINS"|4.36|9.99|0|0|0|6|0|0|0|0|6|"13"|"529"|" "|"NSO"|" "|" "
"ICCC1452"|"CC CHOCOLATE LAB TRIPLETS"|6.88|14.99|0|0|0|0|0|0|0|0|6|"13"|"529"|" "|"NSO"|" "|" "
"ICCC1453"|"CC THREE LITTLE PIGS"|6.88|14.99|2|30|16|1|0|1|15|8|6|"13"|"529"|" "|"NSO"|" "|" "
"ICCC1455"|"CC OUTBACK KOALA FAMILY"|12.12|22.99|2|46|22|7|0|1-|17-|5-|6|"13"|"529"|" "|"NSO"|" "|" "
"ICCC1457"|"CC BUCKLEY DEER FAMILY"|12.12|24.99|3|75|41|5|0|1-|18-|7-|6|"13"|"529"|" "|"NSO"|" "|" "
"ICCC1458"|"BORDER COLLIE FAMILY"|12.12|24.99|1|25|13|6|0|0|0|0|6|"13"|"529"|" "|"NSO"|" "|" "

 

[combo]

Use OpenText method instead of Open.

combo

 

[SkipVought]

Use Data > Get External Data > Text files...

Figure out how it works, then turn on your macro recorder and record INPORTING the data withe the appropriate column types.

Post back with your recorded code to get help customizing.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MJV8198]

Thank you for you responses, I tried adding text to the open but it did not work. I may not have been doing it correctly. I tried the external data and it seems to work but as SkipVought pointed out it needs customizing.

Here is the current Macro.

Sub Import_Data1()
'
' Import_Data1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Sheets("data").Select
Columns("B:BF").Select
Selection.QueryTable.Delete
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\mvarda\Desktop\IMSBDP.355", Destination:=Range("$B$1"))
.Name = "IMSBDP_1"
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(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

I believe the problem areas are in Red. The file will always be on the desktop but it will be different users, the file name will change. The files may not be a text file so i need to see all files.

Thank you for your help.

 

[combo]

In reference to my post, to verify the code, make a copy of your file and rename it's extension to "txt". Next record macro when you open this file, configure the wizard according to your needs.

combo