How to append .xls file as sheet in new workbook from the specified folder

[koksamsun]

Hi all

I've created a some sheets of .xls file like
sample_sheet1.x ls
sample_sheet2.x ls
sample_sheet3.x ls

Now i want to append these three sheets.xls in sample.xls main file in same folder.

I tried this same case for .csv files. It works. But when i tried with .xls files by using Macro i couldn't get.

Could you please anyone help me on this.

Thanks in advance

[jhardman]

I assume the problem is that the function you are using pulls in individual sheets and puts them together into one xls file. CSVs are by definition one sheet only, so you had no problem, but XLS files can contain multiple sheets, so the function you are using is failing. Show the file you have and we will see if we can find a solution.

Jared

[koksamsun]

Hi,
Thanks for your guidance.

My Macro code is below

Sub GetExcelFileData() 
Dim strFilePath As String, strFilename As String, strFullPath As String 
Dim lngCounter As Long 
Dim oConn As Object, oRS As Object, oFSObj As Object 
Dim fileItem As Object 
 
Application.ScreenUpdating = False 
 
'This gives us a full path name e.g. C:tempfolderfile.txt 
'We need to split this into path and file name 
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT") 
 
Set srcFolder = oFSObj.GetFolder(ThisWorkbook.Path) 
 
'Open an ADO connection to the folder specified 
Set oConn = CreateObject("ADODB.CONNECTION") 
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & ThisWorkbook.Path & ";" & _ 
"Extended Properties='Excel 8.0;HDR=Yes';" 
 
Set oRS = CreateObject("ADODB.RECORDSET") 
 
lngCounter = 1 
 
'Now actually open the excel file and import into Excel 
For Each fileItem In srcFolder.Files 
 
strFile = fileItem.Name 
 
If Right(fileItem.Name, 4) = ".xls" And InStr(fileItem.Name, Left(strFile, InStr(strFile, ".") - 1)) = 1 Then 
 
oRS.Open "SELECT * FROM " & strFile, oConn, 3, 1, 1 
While Not oRS.EOF 
If lngCounter > 1 Then 
Sheets.Add After:=Worksheets(Worksheets.Count) 
Else 
lngCounter = 2 
End If 
 
ActiveSheet.Range("A1").CopyFromRecordset (oRS) 
Wend 
oRS.Close 
Columns("A:IV").AutoFit 
End If 
Next 
 
oConn.Close 
 
ActiveWorkbook.Saved = True 
 
End Sub

Actually i created the sample_sheets.x ls files using HTML tags and trying to append these sheets in main sample.xls file as a sheets.

[jhardman]

OK, then what I told you earlier is definitely what is going on, I've used the ADODB.recordet to open an excel file before, but never like this. Basically, the ADODB object is looking for something it can interpret as a data table. CSV files are very simple data tables, so that gave you no problem, but the xls files are not. If you want to use the ADODB method then you will need to specify the sheet name. I think instead you will want to use a different object. Let me see if I can find some code for you.

Jared

[koksamsun]

Thank for your reply and waiting for the solution.

[jhardman]

I'm sorry Koksamsun, I was reading through your code just now, feeling guilty for not getting to it sooner, and I realized that you didn't post in the right forum. when people post code in C# it's obvious, but you were writing in VB.NET which is close enough to VBScript that I didn't even notice. I assume you want this in the ASP.NET forum? This is the classic ASP forum (only answers in VBScript or possibly JScript if anyone really begs).

Jared