Openeing two text text files in the same workbook?? 1

[hanglam]

Hi,

I have a VBA Excel macro that open two text files using the following commands

Workbooks.OpenTexFile file1
Workbooks.OpenTexFile file2

But whenver I run the Macro it creates two Workbooks for me, each workbook containing each file.

My questions is : how do I open two text files in the same WorkBook but in two different WorkSheets (each WorkSheet containing each file).

Thanks,
Hang

 

[HughLerwill]

'You should consider use of QueryTable because that allows you to specify the destination of the 'import'
' the following was originally written for vb6 but should run in Excel VBA and assumes path names to the files to be imported
' are stored in the CsvFileNames() String array.
'It is assumed that a sufficient number Sheets have been previously created to accomodate the number of files to be imported
' although that could be done on-the-fly.

For i = 1 To nCsvFiles
'Label1 = "Excel is loading table " & CsvFileNames(i) & vbCr & i & " of " & nCsvFiles
a$ = CsvFileNames(i)
'accomodate euro qwirks in files with .csv extension
If Mid$(Format$(0.1, "fixed"), 2, 1) <> "." Then 'the decimal character is not a period
a$ = Replace$(CsvFileNames(i), ".csv", ".txt")
Name CsvFileNames(i) As a$
End If
With Workbooks(dest$).Sheets(i + 1)
With .QueryTables.Add(Connection:="TEXT;" & a$, Destination:=.Range("A1"))

'remmed items unrequired or have their values set by default
'.Name = Left$(source$, Len(source$) - 4)
'.Name = Mid$(CsvFileNames(i), InStrRev(CsvFileNames(i), "\") + 1, Len(CsvFileNames(i)) - 4)
'.FieldNames = True
'.RowNumbers = False
'.FillAdjacentFormulas = False
'.PreserveFormatting = True
'.RefreshOnFileOpen = False
'.RefreshStyle = xlInsertDeleteCells
'.SavePassword = False
'.SaveData = True
'.RefreshPeriod = 0
'.TextFilePromptOnRefresh = False
'.TextFileStartRow = 1
'.TextFileConsecutiveDelimiter = False
'.TextFileSemicolonDelimiter = False
'.TextFileSpaceDelimiter = False
'.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
'.TextFileTrailingMinusNumbers = True 'only good in XP and later

.AdjustColumnWidth = False
.TextFilePlatform = xlWindows
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileTabDelimiter = Mid$(Format$(0.1, "fixed"), 2, 1)() <> "." 'euro qwirks
.TextFileCommaDelimiter = Mid$(Format$(0.1, "fixed"), 2, 1)() = "." 'euro qwirks
.Refresh BackgroundQuery:=False
.Delete 'delete connection to file, data remains in the sheet
End With
.Name = Mid$(CsvFileNames(i), InStrRev(CsvFileNames(i), "\") + 1, Len(CsvFileNames(i)) - InStrRev(CsvFileNames(i), "\") - 4)
.Range(.Cells(4, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Columns.AutoFit
.Activate
.Cells(4, 1).Select
End With
Next

 

[SkipVought]

There is absolutely no reason to strain at the gnat.

It ALL can be done with native Excel Spreadsheet functionality; most likely with NO VBA code at all.

Data > Import External Data > IMPORT

If you need to do more once this is accomplished, simply select either QueryTable, turn on your macro recorder and record EDITING the QueryTable. The resulting code will include your connection string and all property value assignments.

Post back with your recorded code if you need further help.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[hanglam]

Thanks you both, the querytable command did the trick...

Thanks,
Hang