Macro to import file

[braju]

Hi,

I am trying to create a macro to import a data file into an excel sheet. I was able to record a macro,but i was only able to import the same file each time.

Can anyone help me create a macro to open a file popup menu such that i can choose the file to be imported.

I can send you my recorded macro code if you need.
One more thing, this is my first encounter with VBasic.
Please help me........
Thank you.

 

[waderw]

once you get file name make this

fileToOpen = Application.GetOpenFilename("AllFiles Files (*.*), *.*")
If fileToOpen <> False Then
Workbooks.OpenText fileToOpen , Origin:=_
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Range("A6").Select
Else
Exit Sub
End If

 

[braju]

I tried this the Statement:
Workbooks.OpenText fileToOpen , Origin:=_
Becomes all Red, i think there is a synatx error in it

 

[waderw]

delete all "_" and all spaces until you have syntax right. it can all be on one line if you like.

record opening up a text file adn just use teh part you need

 

[braju]

When i record it it takes it as a Query file and everything is changed i have tied different Options But for no help.This is my recorded code:

Sub data1()
'
' data1 Macro
' Macro recorded 5/2/2002 by Raju
'

'
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;G:Prince George CoMonitor Tests20021st QuarterNorthPRN'spg28ns031302.prn" _
, Destination:=Range("A1"))
.Name = "pg28ns031302"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
End Sub

and i don have a clue where i need to put the code you gave me...

 

[braju]

hey waderw,

the code worked after i removed all the spaces and "_".
How do i specify the data to open in a second sheet in the same workbook. this code is opening it in a different workbook

thanks for your help

 

[waderw]

after it opens:

cells.select
selection.copy

workbooks(originalWB).activate
sheets.add
activesheet.name = NewWorksheetinSameWB
range("A1").select
selection.paste
workbooks(inFile).select
workbooks.close savechanges:= false