I must use vbscript first hand.
I am trying to take an xls file, copy all the sheets that it contains into another Workbook, copy the code module(Macro code) to the other workbook and save it. They should be identical but for some reason the stupid code module never gets saved.
I have a macro in the workbook that I need to transfer to the newly created file. I can see the object being changed and the writeBook keeps CodeModule change but does not actually get saved. Can anyone help?
Code
Public Function ExcelFileRead(ByVal SrcFile)
Dim ExcelApplication
Dim readBook, writeBook
Set ExcelApplication = CreateObject("Excel.Application")
ExcelApplication.Visible = FALSE
ExcelApplication.DisplayAlerts = FALSE
Set readBook = ExcelApplication.Workbooks.Open(DOWNLOAD_PATH & SrcFile)
Set writeBook = ExcelApplication.Workbooks.Add
If readBook.VBProject.Protection = 1 Then
'print "GODDAMIT it's protected"
End If
writeBook.Worksheets(1).Delete
writeBook.Worksheets(1).Delete
Dim Sheet
Dim wsCount
Dim dlCount
dlCount = readBook.Worksheets.Count
For each Sheet in readBook.Worksheets
wsCount = writeBook.Worksheets.Count
Sheet.Copy writeBook.Worksheets(wsCount)
Next
writeBook.Worksheets(dlCount+1).Delete
/*start problem*/
Dim rVB, wVB, rString
Set rVB = readBook.VBProject.VBComponents(1).CodeModule
Set wVB = writeBook.VBProject.VBComponents(1).CodeModule
rString = rVB.Lines(1,rVB.CountOfLines)
wVB.AddFromString(rString)[/COLOR]
/* End problem */
'print wVB.Lines(1,wVB.CountOfLines)
writeBook.SaveAs(DOWNLOAD_PATH & "test.xlsx")
writeBook.Close
readBook.Close
ExcelApplication.Quit
Set ExcelApplication = NOTHING
End Function