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