Combining workbooks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Combining workbooks

Author

Topic

srxr9
Starting Member

15 Posts

Posted - 2006-10-02 : 23:46:46

I need to combine two workbooks into one workbook with two worksheets.
For example, if I have one workbook with a worksheet called "Test1" and another workbook with worksheet "Test2", I need to combine these two workbooks into one workbook with two worksheets "Test1" & "Test2".

Any advice on this will be helpful.

Thanks
suresh

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-10-03 : 01:27:44

huh?

is this an excel question?

EDIT:

if it is, just right-click the worksheet and select 'Move or Copy'. Then select the workbook from the dropdown menu. btw, both workbooks should be open when doing this.

-ec

first post for ExcelTeam...

srxr9
Starting Member

15 Posts

Posted - 2006-10-03 : 09:34:16

I need to get this done in VB

Thanks
Suresh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 09:36:59

A valuable tip.

1) Start recording a MACRO
2) Do everything needed, manually, to do the transition
3) Stop the recording
4) Copy and paste the code from the VBA code window to your VB application
5) Rewrite the code so that is fits the platform in VB

There you have it.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 09:42:46

If you still can't figure it out

    Windows("Book1").Activate
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy After:=Workbooks("Book3").Sheets(1)
    Windows("Book2").Activate
    Sheets("Sheet2").Select
    Sheets("Sheet2").Copy After:=Workbooks("Book3").Sheets(2)

Peter Larsson
Helsingborg, Sweden

srxr9
Starting Member

15 Posts

Posted - 2006-10-03 : 12:16:24

Thanks Peter.

Here is the Code I use to combine workbooks.
Note: TargetWorkbook has initially a worksheet "Sheet1"
But the code fails in the Copy After statement

Function Main()

Dim Excel_Application, Source_WorkBook, Target_WorkBook, Source_WorkSheet
Dim SourcePathName, TargetFileName, SheetName, TargetSheet
Dim ArrayVariable, I

SourcePathName = "C:\Documents and Settings\Excel_Test\"
TargetFileName = "C:\Documents and Settings\Excel_Test\Test3.xls"

Set Excel_Application = CreateObject("Excel.Application")
Set Target_WorkBook = Excel_Application.Workbooks.Open(TargetFileName)
Set TargetSheet = "Sheet1"
ArrayVariable = ARRAY("Test1.XLS", "Test2.XLS")

FOR I = LBOUND(ArrayVariable) TO UBOUND(ArrayVariable)
Set Source_WorkBook = Excel_Application.Workbooks.Open(SourcePathName & ArrayVariable(I))
For Each Source_WorkSheet in Excel_WorkBook.WorkSheets
Sheetname = Source_Worksheet.Name
MsgBox (SheetName)
Source_WorkBook.Worksheets(SheetName).COPY AFTER:= Target_WorkBook.Worksheets(TargetSheet)
TargetSheet = SheetName
Next
Source_WorkBook.CLOSE
NEXT
Target_WorkBook.CLOSE

Set Source_WorkBook = Nothing
Set Target_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing

Main = DTSTaskExecResult_Success

End Function

What is wrong in the code?

Thanks
Suresh

srxr9
Starting Member

15 Posts

Posted - 2006-10-03 : 15:42:23

Is there an syntax error in the "Copy after" statement in the code below.
I ran this code as a Excel macro and it worked fine, but when I execute this as an ActiveX script in DTS Package it fails.
Error Description: Statement Expected on line (line where copy after statement is)

FUNCTION Main()

DIM Excel_Application, Source_WorkBook, Target_WorkBook, Source_WorkSheet
DIM SourcePathName, TargetFileName, SheetName
DIM ArrayVariable, I

SourcePathName = "C:\Documents and Settings\SRamanathan\Excel_Test\"
TargetFileName = "C:\Documents and Settings\SRamanathan\Excel_Test\Test3.xls"

SET Excel_Application = CreateObject("Excel.Application")

ArrayVariable = ARRAY("Test1.xls", "Test2.xls")

FOR I = LBOUND(ArrayVariable) TO UBOUND(ArrayVariable)
SET Target_WorkBook = Excel_Application.Workbooks.OPEN(TargetFileName)
SET Source_WorkBook = Excel_Application.Workbooks.Open(SourcePathName & ArrayVariable(I))
For Each Source_WorkSheet in Source_WorkBook.WorkSheets
Sheetname = Source_Worksheet.NAME
Excel_Application.DisplayAlerts = False
Source_WorkBook.Worksheets(SheetName).COPY AFTER:= Target_WorkBook.Worksheets(Target_WorkBook.Worksheets.Count)
Target_WorkBook.Save
Excel_Application.DisplayAlerts = True
Target_WorkBook.CLOSE
NEXT
Source_WorkBook.CLOSE
NEXT

SET Source_WorkBook = Nothing
SET Target_WorkBook = Nothing
Excel_Application.Quit
SET Excel_Application = Nothing

Main = DTSTaskExecResult_Success

END FUNCTION

Thanks
Suresh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-03 : 16:01:28

named arguments are probably not supported in VBScript. Remember that vb6 <> vbscript <> vba <> vb.net

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources