Copying sheets from one workbook to another automatically from the command line

[GeorgerV]

Hi Guys.
I have two XLSX files. All sheets from the Second file should be copied to the First file from the end. But everything should be done automatically via command line.
How can I do that?
Please, describe everything step by step, I am a very new in excel automation.
I have found a code for VBA:
-------------------
Sub CopySheets()
Workbooks("Book1.xlsx").Sheets("Sales").Copy _
After:=Workbooks("Dbook.xlsx").Sheets(Workbooks("Dbook.xlsx").Sheets.Count)
Workbooks("Book1.xlsx").Sheets("Marketing").Copy _
After:=Workbooks("Dbook.xlsx").Sheets(Workbooks("Dbook.xlsx").Sheets.Count)
Workbooks("Book1.xlsx").Sheets("Operations").Copy _
After:=Workbooks("Dbook.xlsx").Sheets(Workbooks("Dbook.xlsx").Sheets.Count)
End Sub
-------------------
It seems what I need, but how can I use it in the command line?
Thanks in advance.

 

[breynolds0431]

Hi Guys.
I have two XLSX files. All sheets from the Second file should be copied to the First file from the end. But everything should be done automatically via command line.
How can I do that?
Please, describe everything step by step, I am a very new in excel automation.
I have found a code for VBA:
-------------------
Sub CopySheets()
Workbooks("Book1.xlsx").Sheets("Sales").Copy _
After:=Workbooks("Dbook.xlsx").Sheets(Workbooks("Dbook.xlsx").Sheets.Count)
Workbooks("Book1.xlsx").Sheets("Marketing").Copy _
After:=Workbooks("Dbook.xlsx").Sheets(Workbooks("Dbook.xlsx").Sheets.Count)
Workbooks("Book1.xlsx").Sheets("Operations").Copy _
After:=Workbooks("Dbook.xlsx").Sheets(Workbooks("Dbook.xlsx").Sheets.Count)
End Sub
-------------------
It seems what I need, but how can I use it in the command line?
Thanks in advance.

Some questions:

1) Will the worksheet locations change?
2) Will the file names change?

If you want this set up to process on the same two files, then that would be fairly easy. But, if you need to specify both files and their locations each time, that would be a bit more complicated by ensuring you can automate with variable names and locations.

Also, how would you want to run the code? You'll need a macro-enabled workbook (.XLSM) to house the code and either a command button or running the macro manually (View > Macros) to actually run the code.

The below would assume that the paths and filenames would not change and that you'd be running the code named CopySheets in an .XLSM workbook using View > Macros.

This would go in a normal Module in your .XLSM workbook.

Option Explicit

Sub CopySheets()

'Make some declarations for easier reference
Dim wb1 As Workbook, wb2 As Workbook, ws As Worksheet
Dim sPath As String

' will be alerted if there's an issue with your path or filenames below
On Error GoTo errhandler

'turns off screenupdating
Application.ScreenUpdating = False

Set wb1 = Workbooks.Open(Filename:="C:\Desktop\book1.xlsx") 'Change the path and book name in parathensis to suit
Set wb2 = Workbooks.Open(Filename:="C:\Desktop\book2.xlsx") 'Change the path and book name in parathensis to suit

'Use a Loop to run through all wb2's sheets (i.e.; ws) and copy to wb1
For Each ws In wb2.Sheets
          ws.Copy after:=wb1.Sheets(wb1.Sheets.Count)
Next ws

'stops any interrupting excel alerts
Application.DisplayAlerts = False
         
          'close and save wb1
          wb1.Close savechanges:=True
         
          'close without saving wb2
          wb1.Close savechanges:=False

'turns back on excel alerts
Application.DisplayAlerts = True

'turns back on screenupdating
Application.ScreenUpdating = True
Exit Sub

'below will process if an error is noted anywhere in the sub
errhandler:
MsgBox "There was a problem with finding the specified files.", vbCritical, "File Issue"
Application.ScreenUpdating = True
Exit Sub

End Sub

 

[breynolds0431]

I just noticed an error in the workbook.close where wb1 was being closed twice instead of both workbooks. Updated below.

Option Explicit

Sub CopySheets()

'Make some declarations for easier reference
Dim wb1 As Workbook, wb2 As Workbook, ws As Worksheet
Dim sPath As String

' will be alerted if there's an issue with your path or filenames below
On Error GoTo errhandler

'turns off screenupdating
Application.ScreenUpdating = False

Set wb1 = Workbooks.Open(Filename:="C:\Desktop\book1.xlsx") 'Change the path and book name in parathensis to suit
Set wb2 = Workbooks.Open(Filename:="C:\Desktop\book2.xlsx") 'Change the path and book name in parathensis to suit

'Use a Loop to run through all wb2's sheets (i.e.; ws) and copy to wb1
For Each ws In wb2.Sheets
          ws.Copy after:=wb1.Sheets(wb1.Sheets.Count)
Next ws

'stops any interrupting excel alerts
Application.DisplayAlerts = False
         
          'close and save wb1
          wb1.Close savechanges:=True
         
          'close without saving wb2
          wb2.Close savechanges:=False

'turns back on excel alerts
Application.DisplayAlerts = True

'turns back on screenupdating
Application.ScreenUpdating = True
Exit Sub

'below will process if an error is noted anywhere in the sub
errhandler:
MsgBox "There was a problem with finding the specified files.", vbCritical, "File Issue"
Application.ScreenUpdating = True
Exit Sub

End Sub

 

[GeorgerV]

Thanks a lot for your answer.
1. The place will be always the same.
2. The file names will be the same too.

I am going to do that from my application, so I can copy files to the temporary folder with temporary names,
The main goal is to do that AUTOMATICALLY from another application, NOT manually in Graphic Interface.
So the aim is to use the command line and run Excel preferably in the hide mode.
I am a very new person in VBA+Excel.
If you have a solution, please, describe all steps how to do that.
Thanks again.