Can we import a file using macro or VBA code

[c_rahul001]

If i want to import text file using VBA or Macro i can use below code


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

"Select Text Data File")


but does not work if we need to perform few steps Under file origin section after opening the file using above code. Is there any other way to import file and then macro should perform few task Under file origin section of Text Import Wizard under Data tab.


I want macro to select file which user want and then perform few task on the file selected.


I have recorded a macro which select the file directly and i have no option of selecting a file which i want. However it process the code with the output but need to change the path for every time when i execute the macro for other file. Below is the code for same.


Sub Macro1()

'

' Macro1 Macro

'

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:RahulZh....ZS Re...2012May 20122012.05.0720111231003.txt" _

, Destination:=Range("$A$1"))

.Name = "20111231003"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 936

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = False

.TextFileSpaceDelimiter = False

.TextFileOtherDelimiter = "|"

.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

End Sub

 

[SirJB7]

Hi, c_rahul001!


You passed from a simple opening a text file to a complex web query. There's a big difference between those methods, on how, when and why the should be used.


If you want to give a look at this file, you'll find a full example of web query:

http://chandoo.org/forums/topic/macros-web-query-huge-headache-can-anyone-help-me#post-21709


If you need a different thing (which I suppose as of your "need to perform few steps Under file origin section after opening the file"), please do the following:

a) prepare and upload a sample file with the specs for the web query

b) include placing details within your workbook

c) write down those "few steps" and explain what do you want to do


This will be very useful for people who might be able to help you.


Regards!

 

[c_rahul001]

Hi, SirJB7,


Thank you. I guess it is really very difficult to make others know what we want and need to do for the required output until and unless one upload the sample file, if the required output is complex.


I was looking out for the code which accept only the path of text file from the user and after that it performs few steps on it under the Text Import Wizard once we have selected the file. The steps i want to perform under "Text Import Wizard" are below.

>>> Under Tab section > Data > Get External Data > From Text > select file to be processed > Import the file.

>>> Then you get the window of Text Import Wizard


Under "Text Import Wizard" i wanted to perform following steps:


Step 1 – Under file origin section Select “936: Chinese Simplified”(GB2312) and click > Next >

Step 2 – Unmark the check box "Tab" and Select the check box named “Other” and enter “|” as the Delimiters > click Next >

Step 3 – (a) Select column "ABC" under Data Preview and select "Text" under Column data format.

(b) Select column "XYZ" under Data Preview and select "Text" under Column data format > click Finish.


But i have got through it and have pasted the code below for same.


Sub CommandButton1_Click()


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

"Select Todays Data Text File")

If Filename = False Then Exit Sub


Sheets("Sheet2").Select


With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;" & Filename, Destination:=Range("$A$1"))

.Name = Filename

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 936

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = False

.TextFileSpaceDelimiter = False

.TextFileOtherDelimiter = "|"

.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With


End Sub

 

[SirJB7]

Hi, c_rahul001!

Glad you could solve it. Thank you very much for your feedback and welcome back whenever needed or wanted.

Regards!

 

[c_rahul001]

Sure, will definitely require help in future. Thanks for the support and help.