Help Importing using GetOpenFilename
 

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

Help Importing using GetOpenFilename
 

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




"Adriana" wrote:

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

Help Importing using GetOpenFilename
 

"Adriana" wrote:

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

Help Importing using GetOpenFilename
 

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" wrote:

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




"Adriana" wrote:

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

Help Importing using GetOpenFilename
 

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"))


"Joel" wrote:

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" wrote:

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




"Adriana" wrote:

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

Help Importing using GetOpenFilename
 

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" wrote:

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"))


"Joel" wrote:

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" wrote:

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




"Adriana" wrote:

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

Help Importing using GetOpenFilename
 

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 a
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


"Adriana" wrote:

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" wrote:

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"))


"Joel" wrote:

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" wrote:

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




"Adriana" wrote:

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