Error Message regarding .TextFilePlatform = 437
 

I've written a macro with a getDirectory(StrPath) so that the user can choose
where to get the txt file to import from. The problem is that it works fine
on my PC as well as a laptop but for some reason don't work on a workstation
where I tried to implement the code.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & StrPath, Destination _
:=Range("A1"))
.Name = "SeeName"
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 9, 1, 1, 1,
1, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Can someone please help me to correct this so that I can implement the
application on any machine.

Error Message regarding .TextFilePlatform = 437
 

is the version of excel on the machine where it fails an older version than
the others.
If so, then record youself manually setting up the querytable in that older
version. Compare the recorded code to the code you have. Remove items that
are not found in both versions.

--
Regards,
Tom Ogilvy


"Sue" wrote:

I've written a macro with a getDirectory(StrPath) so that the user can choose
where to get the txt file to import from. The problem is that it works fine
on my PC as well as a laptop but for some reason don't work on a workstation
where I tried to implement the code.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & StrPath, Destination _
:=Range("A1"))
.Name = "SeeName"
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 9, 1, 1, 1,
1, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Can someone please help me to correct this so that I can implement the
application on any machine.

Error Message regarding .TextFilePlatform = 437
 

Sue,
According to XL2002 Help, which to me is somewhat misleading:
"TextFilePlatform Property
Returns or sets the origin of the text file you're importing into the query
table. This property determines which code page is used during the data
import. The default value is the current setting of the File Origin option
in the Text File Import Wizard. Read/write XlPlatform.
XlPlatform can be one of these XlPlatform constants.
xlMacintosh
xlMSDOS
xlWindows "

And the Object Browser shows values of XlPlatform to be:
Const xlMacintosh = 1
Const xlWindows = 2
Const xlMSDOS = 3

However, this is really the code page to use for the import, where 437
refers to "OEM United States".

So not sure what the problem is, but Tom's reply probably applies.

NickHK

"Sue" wrote in message
...
I've written a macro with a getDirectory(StrPath) so that the user can
choose
where to get the txt file to import from. The problem is that it works
fine
on my PC as well as a laptop but for some reason don't work on a
workstation
where I tried to implement the code.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & StrPath, Destination _
:=Range("A1"))
.Name = "SeeName"
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 9, 1, 1,
1,
1, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Can someone please help me to correct this so that I can implement the
application on any machine.

Error Message regarding .TextFilePlatform = 437
 

Thanks, will try.

"Tom Ogilvy" wrote:

is the version of excel on the machine where it fails an older version than
the others.
If so, then record youself manually setting up the querytable in that older
version. Compare the recorded code to the code you have. Remove items that
are not found in both versions.

--
Regards,
Tom Ogilvy


"Sue" wrote:

I've written a macro with a getDirectory(StrPath) so that the user can choose
where to get the txt file to import from. The problem is that it works fine
on my PC as well as a laptop but for some reason don't work on a workstation
where I tried to implement the code.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & StrPath, Destination _
:=Range("A1"))
.Name = "SeeName"
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 9, 1, 1, 1,
1, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Can someone please help me to correct this so that I can implement the
application on any machine.