Connect two xls files

[marinosandria]

Dear all i want to connect together on the same sheet two different xls files. I am using the following code :

mypath = "C:\interfaces\"
destfile = "C:\interfaces\debitlist.xls"
srcfile = Dir(mypath & "*.xls")
Do While Len(srcfile) > 0
Open mypath & srcfile For Input As 1
filestr = Input(LOF(1), #1)
Close 1
Open destfile For Append As 1
Print #1, filestr
Close 1
srcfile = Dir
Loop

but i am getting the following error
INPUT PAST END OF FILE on the 6th line .
Does anybody knows how can i do it.

Thanks
marinosandria

[anhn]

Excel files are not text files. You cannot append one .xls file to another .xls like that.

[marinosandria]

Excel files are not text files. You cannot append one .xls file to another .xls like that.

Can you please anyone help me how can i do it?

Thanks

[westconn1]

try like this, but i don't promise there will not be some blank rows between appended sheets

Sub appendall()
destbk As Object, srcbk As Object, strtrow As Integer, mypath As String, srcfile As String
mypath = "C:\interfaces\"
Set destbk = Workbooks.Open("C:\interfaces\debitlist.xls")
srcfile = Dir(mypath & "*.xls")
Do While Len(srcfile) > 0
''Open mypath & srcfile For Input As 1
''filestr = Input(LOF(1), #1)
''Close 1
strtrow = destsht.UsedRange.Rows.Count + 1
Set srcbk = Workbooks.Open(mypath & srcfile)
'******* if you want some header between added sheets, put next 2 lines
destbk.range("a1") = srcfile
strtrow = strtrow +1
'*********
srcbk.Sheets("sheet1").UsedRange.Copy destbk.Sheets("sheet1").Range("a" & strtrow)
srcbk.Close false
''Open destfile For Append As 1
''Print #1, filestr
''Close 1
srcfile = Dir
Loop
destbk.Save
destbk.Close
End Sub

[marinosandria]

Thanks for your advise, but to be more specific, i have several xls files in one folder called interfaces and i want to merge them all into on worksheet called debit list.xls.
I have tried to many codes but i couldnt made it since now. I can do that for .txt and .csv files but not for .xls files.

If anyone of you can help me i will be very greatfull.

Thanks
marinosandria

[westconn1]

did you try the code i posted? did it give error or wrong result?
if error what error and at which line?
if wrong result what was incorrect?

[marinosandria]

Dear Westconn1 i have tried your code and i got an error on line 10. It doesnt recognize the variable destsht. That is logical because is not defined at the begining of the project. Shall i define it as an object?

Thanks
marinosandria

[anhn]

Try this:

Sub AppendAllSheet1s()
    Dim sPath   As String
    Dim wb0     As Workbook
    Dim ws0     As Worksheet
    Dim sFname0 As String
    Dim wb      As Workbook
    Dim ws      As Worksheet
    Dim sFname  As String
    Dim r       As Long
    
    sPath = "C:\interfaces\"
    sFname0 = "debitlist.xls"
    If Len(Dir(sPath & sFname0)) Then
        Set wb0 = Workbooks.Open(sPath & sFname0)
    Else
        Set wb0 = Workbooks.Add()
    End If
    Set ws0 = wb0.Worksheets(1)
    sFname = Dir(sPath & "*.xls")
    Do While Len(sFname)
        If sFname <> "debitlist.xls" Then
            r = ws0.UsedRange.Row + ws0.UsedRange.Rows.Count
            Set wb = Workbooks.Open(sPath & sFname, False, True)
            wb.Worksheets(1).UsedRange.Copy ws0.Cells(r, 1)
            wb.Close False
        End If
        sFname = Dir()
    Loop
    Set wb = Nothing
    If Len(wb0.Path) Then
        wb0.Save
    Else
        wb0.SaveAs sPath & sFname0
    End If
    'wb0.Close
    Set ws0 = Nothing
    Set wb0 = Nothing
End Sub

[westconn1]

i have tried your code and i got an error on line 10

line 10 should have read
strtrow = destbk.sheets("sheet1").UsedRange.Rows.Count + 1
i changed some of the code, but missed that line
of course you can declare and set a sheet object instead

[marinosandria]

Dear anhn i have tried your code and its working fine. The only that is missing is the closing of the workbook , but i did by myself.

Thank You Very Much, You Are Very Helpful.

marinosandria

[westconn1]

at the end (where 'wb0.close) put wb0.activate and it should show to the user
the only way to run an excel file is in excel, it is only a matter of tidying up any loose ends

[marinosandria]

Dear all, i would like to thank you for your usefull information. After your help i have finished what i wanted to do.

Thanks
marinosandria

[anhn]

Please mark your thread as Resolved.