[VB2005]Import textfiles to Excel using QueryTable

bellaelysium · July 28th, 2008, 01:32 AM

Hi,

it was the first time I did regarding excel.QueryTable in VB 2005 .

What I am trying to do is to import data from many textfiles to worksheets in ONE workbook.

But there's seem to be some trouble opening.

Code:

 For n = 0 To ListBox1.Items.Count - 1

            selectedfile = ListBox1.Items(n).ToString
            fullpath = System.IO.Path.GetFullPath(selectedfile)
 Dim FileNameOnly1 As String = System.IO.Path.GetFileNameWithoutExtension(fullpath)

Dim j As Integer = 0
            For Each WSheet In WBook.Sheets.Add(selectedfile)

                j += 1
                If j = 1 Then

                    With WSheet.QueryTables.Add(Connection:="TEXT;" & fullpath _
& ".txt", Destination:=range("A1"))


                        .Name = FileNameOnly1
                        .FieldNames = True
                        .RowNumbers = False
                        .FillAdjacentFormulas = False
                        .PreserveFormatting = True
                        .RefreshOnFileOpen = False
                        .RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells
                        .SavePassword = False
                        .SaveData = True
                        .AdjustColumnWidth = True
                        .RefreshPeriod = 0
                        .TextFilePromptOnRefresh = False
                        .TextFilePlatform = 437
                        .TextFileStartRow = 1
                        .TextFileParseType = XlTextParsingType.xlDelimited
                        .TextFileTextQualifier = _
XlTextQualifier.xlTextQualifierDoubleQuote
                        .TextFileConsecutiveDelimiter = False
                        .TextFileTabDelimiter = True
                        .TextFileSemicolonDelimiter = False
                        .TextFileCommaDelimiter = False
                        .TextFileSpaceDelimiter = False
                        .TextFileColumnDataTypes = .Array(1, 1, 1, 1, 1, 1, 1, 1)
                        .TextFileTrailingMinusNumbers = True
                        .Refresh(BackgroundQuery:=False)

                    End With

                    Continue For
                Else
                    WSheet.Delete()
                End If
            Next

        Next n

I have change from this:

Code:

'With WSheet.QueryTables.Add_(Connection:= "TEXT;D:\project1_
\Elysium\code and excel\spreadsheet\10 June 2008.txt",Destination:=range_
("A1"))

to this:

Code:

 With WSheet.QueryTables.Add(Connection:="TEXT;" & fullpath 
_& ".txt", Destination:=range("A1"))

The error point to this line:

Code:

For Each WSheet In WBook.Sheets.Add(selectedfile)

Sorry, can anyone help?

thanks :)