Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
QM2 & Excel - Move a tab to a different file
#1
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
#2
In Move try to replace 0 with @.
#3
When I substitute the @ character, I get the error: "No such interface supported."

I'm not sure what that means.

Thanks,
Phil
#4
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! Smile
--Phil
#5
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


Forum Jump:


Users browsing this thread: 2 Guest(s)