Import Data macro

[KHashmi316]

I'm trying to automate the Import Data process somewhat; hence I recorded the "IMPORT_DATA" macro below. I need a macro that does everything in IMPORT_DATA *except* use the predetermined file path and file name. That is, I want to be prompted to browse for or insert the path and file name. Then, proceed with the rest of the IMPORT_DATA, as seen below. How can this be done?

Thx for any info you can provide.
-KH

Sub IMPORT_DATA()

Cells.Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\Dell_Inspirion5100\G\household-appliances_com\eCost\10273764-eCOST_com_eZ_Affiliate_Program.txt" _
, Destination:=Range("A1"))
.Name = "10273764-eCOST_com_eZ_Affiliate_Program"
.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 = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.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, 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
ActiveWorkbook.Save

End Sub

[anilsolipuram]

try this macro, I am using inputbox which will prompt for complete filename with path.
This filename with path is used inthe macro get the text contents
Sub IMPORT_DATA()

Cells.Select
Dim file_name As Variant
file_name = InputBox("enter the filename with complete file path")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & file_path _
, Destination:=Range("A1"))
.Name = "10273764-eCOST_com_eZ_Affiliate_Program"
.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 = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.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, 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
ActiveWorkbook.Save

End Sub

[KHashmi316]

anilsolipuram, thx for your help!

When I tried it, I got a:

"Compile Error: Valiable not found"

where "file_path" is highlighted in:

"Connection:= _
"TEXT;" & file_path _
, Destination:=Range("A1"))"

Also, how do I deal with this parameter from the original IMPORT-DATA:

.Name = "10273764-eCOST_com_eZ_Affiliate_Program"


-KH

[anilsolipuram]

minor error, try now

Sub IMPORT_DATA()

Cells.Select
Dim file_name As Variant
file_name = InputBox("enter the filename with complete file path")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & file_name _
, Destination:=Range("A1"))
.Name = "10273764-eCOST_com_eZ_Affiliate_Program"
.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 = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.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, 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
ActiveWorkbook.Save

End Sub

[KHashmi316]

Worked like a charm -- thank you!

May I ask for one more tweak? In it's current form, the InputBox asks for the filename with complete file path. Is it possible to design the InputBox so that one can *Browse* for a file. Often I find it helpful to type/paste in a simple directory path (e.g. C:\Excel_files\) and choose the file I wish to import.

-KH

[anilsolipuram]

I set the default filepath as "c:\" , macro will open file dialog to select file.

try this macro


Sub IMPORT_DATA()

dim file_name as variant

Cells.Select



Application.DefaultFilePath = "C:\" 'Set default file path to root
file_name = Application.GetOpenFilename



With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & file_name _
, Destination:=Range("A1"))
.Name = "10273764-eCOST_com_eZ_Affiliate_Program"
.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 = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.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, 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
ActiveWorkbook.Save

End Sub

[KHashmi316]

anilsolipuram,

Your modification does everything I had always hoped this macro would do.

You've been a immense help!

Thx again,
-KH