QM2 & Excel - Move a tab to a different file

[pgomez]

Hello,

I've been using QM2 for several years and it's been a huge help with my work and career. I frequently use it in conjunction with Microsoft Excel. However, I'm stuck on a particular problem. I have a folder with several Excel files, each one has only one tab. I'd like to write a macro that combines each file/tab into one file with several tabs.

To do this manually, I would open a file in Excel and n the Excel GUI, I would right-click the tab name, select "Move or Copy", and then select the options to move the tab to a different file.

I can't quite figure out the VBA commands to accomplish the same things. It seems like it should be either the Workbook.Sheets.Move method or the Workbook.Dialogsheets.Move method. Sadly, I can't get either to work. Here's as far as I've been able to figure out.

Macro Combine Excel files

Code:

Copy      Help

ExcelSheet xl1 xl2
Excel.Workbook wb1 wb2

xl1.Init(0 8|16)
xl1.Save("C:\Users\YOU\Desktop\Example1.xlsx" 2)

xl2.Init(0 8|16)
xl2.Save("C:\Users\YOU\Desktop\Example2.xlsx" 2)

wb1 = xl1._Book
wb2 = xl2._Book

;attempt 1
wb1.DialogSheets.Move(0 xl2) ;; does not work

;attempt 2
wb1.Sheets.Move(0 xl2) ;; does not work

I think I'm not getting the parameters (variant variables?) correct for those methods.

Any suggestions?

For reference, here's the online documentation for the worksheet.move method:
https://docs.microsoft.com/en-us/office/...sheet.move

Any help is very much appreciated,
Phil

[Gintaras]

In Move try to replace 0 with @.

[pgomez]

When I substitute the @ character, I get the error: "No such interface supported."

I'm not sure what that means.

Thanks,
Phil

[pgomez]

I think I may have figured out a way that works. Both of these modifications are working for me now:

Macro Combine Excel files

Code:

Copy      Help

;attempt 1
int i = wb2.Sheets.Count
wb1.DialogSheets.Move(@ wb2.Sheets.Item(i)) ;; works

;attempt 2
wb1.Sheets.Move(@ wb2.Sheets.Item(i)) ;; works

I'm not sure exactly why they are working, but I'm glad they are.

Thanks anyway!
--Phil

[pgomez]

One more note about this issue. I learned that the Move method does not work if the Excelsheets have been Init(ialized) with flag 8. When I switched to using flag 4, that's when the methods worked.

Thanks,
Phil