Venio
asked on
Copy one Excel Table into another one
Hi!
I've got a problem. I need to switch to the right Excel Window (Named DEBITOR-9xxxx...) copy all the lines that have text in another Excel Window (Named ALL) and close the Excel Window afterwards.
The Problem is, that I have to do this with Visual Basic 6.0 (so no VBA commands...) and that I need to repeat this for a number of files (which I have to load)
I hope you can help me!
I've got a problem. I need to switch to the right Excel Window (Named DEBITOR-9xxxx...) copy all the lines that have text in another Excel Window (Named ALL) and close the Excel Window afterwards.
The Problem is, that I have to do this with Visual Basic 6.0 (so no VBA commands...) and that I need to repeat this for a number of files (which I have to load)
I hope you can help me!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In the VB project, select Project | References from the main menu. Scroll down the list to find the Microsoft Excel Object Library (the version will depend on your Office installation ie. v8.0 is for Office 97, v9.0 for Office 2000.)
Check the reference.
Once referenced, you should be OK
Check the reference.
Once referenced, you should be OK
ASKER
I see - thank you - the only problem I still have is, that the data I should combine is in completely different files. For Example a file called FINAL.XLS and DEBITOR90001.XLS ...02.XLS and so on. Ca I do that with the same source?
you can manage multiple workbooks at the same time by setting up additional object variable...
for example ..
set oxlbook1 = oxlapp.workbooks.open("c:\
set oxlbook2 = oxlapp.workbooks.open("c:\
If you have one target worksheet, I suggest you use the original code above
So
for each workbook ....(use dir to loop through a folder)
remove blank rows
'when all blank rows are removed, then select all rows
cells.select ... will select the entire sheet
selection.copy.. will copy the sheet
now, on you target sheet,
select the next available row
(ie. lastrow(sheet) + 1), and then
Selection.paste .. to paste in the copied sheet..
close the workbook
next worksbook
for example ..
set oxlbook1 = oxlapp.workbooks.open("c:\
set oxlbook2 = oxlapp.workbooks.open("c:\
If you have one target worksheet, I suggest you use the original code above
So
for each workbook ....(use dir to loop through a folder)
remove blank rows
'when all blank rows are removed, then select all rows
cells.select ... will select the entire sheet
selection.copy.. will copy the sheet
now, on you target sheet,
select the next available row
(ie. lastrow(sheet) + 1), and then
Selection.paste .. to paste in the copied sheet..
close the workbook
next worksbook
ASKER
thank you very much so far! I will try it and accept your answer as the right one. You've really helped me. It would be great, if you could help if I happen to have a question regarding this topic.
ASKER
I've just tried your sourcecode and whenever I try to run the programm I get the errorcode 1004 saying, that its not possible to name one sheet like another. What suprises me is that the code creates a new excel table.
Please help me again... I need to get this programm running. Can I mail you the code so you can have a slight look at it? WOULD BE REALLY GREAT!
Please help me again... I need to get this programm running. Can I mail you the code so you can have a slight look at it? WOULD BE REALLY GREAT!
can you paste the code in this window?
or you could try this...
after the copy is done, set the
activesheet.name = "New sheet name" ... what ever you like as a name (Up to 30 characters). Include a counter or something else that will uniquely identify the worksheet.
or you could try this...
after the copy is done, set the
activesheet.name = "New sheet name" ... what ever you like as a name (Up to 30 characters). Include a counter or something else that will uniquely identify the worksheet.
ASKER
Thank you very much for your help. I've found a good way to solve the problem. Hope to hear from you again
ASKER