Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SOS:why run error
What is the error? Which line does the error occur?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |