Help Importing using GetOpenFilename

[Adriana]

Hello. I am new to VBA, and I've been having trouble setting up a macro to
import a file using GetOpenFilename.
What I did is I recorded a macro when I imported the file so that all the
code related to editing the imported data was recorded, then I entered the
GetOpenFilename function above that, and substituted the file path for the
strFileName variable.
I know the file path gets saved under strFileName, but it won't open/import
the file, and I don't know what to do! My code is below. I would really
appreciate your help. Thanks!

Dim strFileName As String
Dim strMessage As String

strFileName = Application.GetOpenFilename("LAS Files (*.las), *.las)", ,
"Select Import File")
strMessage = "You selected the '" & strFileName & "' file."
MsgBox strMessage

Sheets("Read Me First").Select
Range("L1").Select
ActiveSheet.Unprotect
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;& strFileName", Destination:=Range( _
"L1"))
.Name = "Data_16"
.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 = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
'.Refresh BackgroundQuery:=False
End With

 

[JMB]

Getopenfilename doesn't actually open the file - it just returns the file
name. You still need to actually open the file.

For example:
Workbooks.Open filename:=strfilename

 

[Joel]

Hello. I am new to VBA, and I've been having trouble setting up a macro to
import a file using GetOpenFilename.
What I did is I recorded a macro when I imported the file so that all the
code related to editing the imported data was recorded, then I entered the
GetOpenFilename function above that, and substituted the file path for the
strFileName variable.
I know the file path gets saved under strFileName, but it won't open/import
the file, and I don't know what to do! My code is below. I would really
appreciate your help. Thanks!

Dim strFileName As String
Dim strMessage As String

strFileName = Application.GetOpenFilename("LAS Files (*.las), *.las)", ,
"Select Import File")
strMessage = "You selected the '" & strFileName & "' file."
MsgBox strMessage

Sheets("Read Me First").Select
Range("L1").Select
ActiveSheet.Unprotect
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;& strFileName", Destination:=Range( _
"L1"))
.Name = "Data_16"
.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 = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
'.Refresh BackgroundQuery:=False
End With

 

[Joel]

You don't need to open the file with this code. You are right that
Getopenfilename doesn't open the file. But the QueryTables.Add will get the
data from the file withoput opening.

 

[JMB]

Thanks for the correction. I see you have another response to this thread,
but I can't see exactly what you suggested (so you may have already pointed
this out). Perhaps the OP's issue is a quote in the wrong place:

Should be:
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strFileName, Destination:=Range("L1"))

 

[Adriana]

Hi JMB,

Thanks for replying. I just tried moving the quote like you show below, but
it still won't import the data
Do you think it might be something later in the code? I really appreciate
your help. Thanks!


Adriana

 

[JMB]

Does the code break and give you an error? I got an error w/TextFilePlatform
of 437. On XL2000, the only settings for this property are:
xlMSDOS = 3
xlWindows= 2
xlMacintosh = 1

I think you are using a later XL version, so double check VBA help for what
is allowed for this property.

Also, I had to uncomment (remove the single apostrophe from) the next to
last line to get it to work:

..Refresh BackgroundQuery:=False