Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs question(s)
When I use "ActiveWorkbook.SaveAs" in the following code I lose focus from
the macro it originated from . '========= With ActiveSheet sSheetName = ActiveSheet.Name If ActiveSheet.Name = "Sheet1" Then GoTo EndIt End If Sheets(sSheetName).Select Sheets(sSheetName).Copy ActiveWorkbook.SaveAs Filename:= _ sNewItem & "\Template ISO " & sSheetName & " Audit mm.dd.yy.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, WriteResPassword:="2000" '========= It seems any commands I use after the above the macro will stop processing unless I use a msgbox prompt right after and that only works under certain variables used or always if I use a string like msgbox "ok"? After clicking on the msgbox prompt the macro appears to continue on. There are no errors being reported. My intention is to create a new workbook (wb) from an existing wb, add a worksheet to the new wb, rename the worksheet in the new wb and continue on with more code but I can not get past this stumbling block. -- Regards Rick XP Pro Office 2007 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs question(s)
Use THISWORKBOOK to access data from the macro where the code is running
from. Open workbooks switches focus to the new workbook. After the workbook is open I usally put the following statement to allow me to go back and forth between THISWORKBOOK and the new workbook workbooks.open filename:= filename set newwbk = activeworkbook 'some code working on newly opened book thisworkbook.activate 'some more code newworkbook.activate "Rick S." wrote: When I use "ActiveWorkbook.SaveAs" in the following code I lose focus from the macro it originated from . '========= With ActiveSheet sSheetName = ActiveSheet.Name If ActiveSheet.Name = "Sheet1" Then GoTo EndIt End If Sheets(sSheetName).Select Sheets(sSheetName).Copy ActiveWorkbook.SaveAs Filename:= _ sNewItem & "\Template ISO " & sSheetName & " Audit mm.dd.yy.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, WriteResPassword:="2000" '========= It seems any commands I use after the above the macro will stop processing unless I use a msgbox prompt right after and that only works under certain variables used or always if I use a string like msgbox "ok"? After clicking on the msgbox prompt the macro appears to continue on. There are no errors being reported. My intention is to create a new workbook (wb) from an existing wb, add a worksheet to the new wb, rename the worksheet in the new wb and continue on with more code but I can not get past this stumbling block. -- Regards Rick XP Pro Office 2007 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs question(s)
hi
something odd about your code. you select a sheet then copy that sheet but don't do anthing with the copied data. ?!?! try this.... Sheets(sSheetName).Select Sheets(sSheetName).Copy Workbooks.Add '********* Range("A1").PasteSpecial xlPasteAll '********* ActiveWorkbook.SaveAs Filename:= _ sNewItem & "\Template ISO " & sSheetName & " Audit mm.dd.yy.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, _ WriteResPassword:="2000" Activeworkbook.close '********* Adding a workbook would make the new workbook the active workbook and when you close the active workbook, your macro book should still be open and running. untested. using xp here. Regards FSt1 "Rick S." wrote: When I use "ActiveWorkbook.SaveAs" in the following code I lose focus from the macro it originated from . '========= With ActiveSheet sSheetName = ActiveSheet.Name If ActiveSheet.Name = "Sheet1" Then GoTo EndIt End If Sheets(sSheetName).Select Sheets(sSheetName).Copy ActiveWorkbook.SaveAs Filename:= _ sNewItem & "\Template ISO " & sSheetName & " Audit mm.dd.yy.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, WriteResPassword:="2000" '========= It seems any commands I use after the above the macro will stop processing unless I use a msgbox prompt right after and that only works under certain variables used or always if I use a string like msgbox "ok"? After clicking on the msgbox prompt the macro appears to continue on. There are no errors being reported. My intention is to create a new workbook (wb) from an existing wb, add a worksheet to the new wb, rename the worksheet in the new wb and continue on with more code but I can not get past this stumbling block. -- Regards Rick XP Pro Office 2007 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs question(s)
Thank you Joel and FSt1 for your replies.
It turns out you have to be very specific where your code goes to move from sheet to sheet. I simply had to work out where the code should be after activating a worksheet. -- Regards Rick XP Pro Office 2007 "Rick S." wrote: When I use "ActiveWorkbook.SaveAs" in the following code I lose focus from the macro it originated from . '========= With ActiveSheet sSheetName = ActiveSheet.Name If ActiveSheet.Name = "Sheet1" Then GoTo EndIt End If Sheets(sSheetName).Select Sheets(sSheetName).Copy ActiveWorkbook.SaveAs Filename:= _ sNewItem & "\Template ISO " & sSheetName & " Audit mm.dd.yy.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, WriteResPassword:="2000" '========= It seems any commands I use after the above the macro will stop processing unless I use a msgbox prompt right after and that only works under certain variables used or always if I use a string like msgbox "ok"? After clicking on the msgbox prompt the macro appears to continue on. There are no errors being reported. My intention is to create a new workbook (wb) from an existing wb, add a worksheet to the new wb, rename the worksheet in the new wb and continue on with more code but I can not get past this stumbling block. -- Regards Rick XP Pro Office 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with ActiveWorkbook.SaveAs | Excel Programming | |||
ActiveWorkbook.SaveAs | Excel Programming | |||
Set mySavedSummary = ActiveWorkbook.SaveAs( ....... | Excel Programming | |||
ActiveWorkbook.SaveAs Problem | Excel Programming | |||
activeworkbook.saveas | Excel Programming |