SOS:why run error
 

HI,
I run this code ,but always error,why? how to change?


Sub Macro1()
Dim filename
filename = Application.GetOpenFilename("Text Files (*.*), *.*", , "plese
select file", , MultiSelect:=False)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;& filename", Destination:= _
Range("A1"))
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 2, 1)
.TextFileFixedColumnWidths = Array(20, 12, 21, 13, 22, 35)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False .....................error
End With
End Sub


Thanks in advance

SOS:why run error
 

What is the error? Which line does the error occur?

SOS:why run error
 

Can you post the original code that worked before you made modifications?

Is the file fixed column length or tab delimited?

You are probably getting an error becuase of the fixed width data. Try
removing these two lines.

.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 2, 1)
.TextFileFixedColumnWidths = Array(20, 12, 21, 13, 22, 35)

If your data doesn't match these column widths yo umay get an error.
Without these lines the code wil look for Tab Delimited Data.


If this doesn't work try re-recording a macro when yo uread in the data. Do
not use fixed column width if possible.



"xcweibing" wrote:

HI,
I run this code ,but always error,why? how to change?


Sub Macro1()
Dim filename
filename = Application.GetOpenFilename("Text Files (*.*), *.*", , "plese
select file", , MultiSelect:=False)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;& filename", Destination:= _
Range("A1"))
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 2, 1)
.TextFileFixedColumnWidths = Array(20, 12, 21, 13, 22, 35)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False .....................error
End With
End Sub


Thanks in advance

SOS:why run error
 

Move the second double quote from after & filename to after TEXT;

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & filename, Destination:= _

When filename is included inside the double quotes it is just the string
"flename" instead of the variable filename.

Hope this helps,

Hutch

"xcweibing" wrote:

HI,
I run this code ,but always error,why? how to change?


Sub Macro1()
Dim filename
filename = Application.GetOpenFilename("Text Files (*.*), *.*", , "plese
select file", , MultiSelect:=False)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;& filename", Destination:= _
Range("A1"))
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 2, 1)
.TextFileFixedColumnWidths = Array(20, 12, 21, 13, 22, 35)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False .....................error
End With
End Sub


Thanks in advance