SOS:why run error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xcweibing - 24 Mar 2008 18:17 GMT

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 to this Message

dan dungan - 24 Mar 2008 18:39 GMT

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

Reply to this Message

Joel - 24 Mar 2008 18:43 GMT

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.

> HI,
> I run this code ,but always error,why? how to change?
[quoted text clipped - 35 lines]
>
> Thanks in advance

Reply to this Message

Tom Hutchins - 24 Mar 2008 18:45 GMT

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

> HI,
> I run this code ,but always error,why? how to change?
[quoted text clipped - 35 lines]
>
> Thanks in advance

Reply to this Message