Problems executing commands on one file from another
in [Programming]
|
From: Eric_G on 7 May 2010 17:27 Thanks, I'll try that. Is it possible that there's something wrong with the command "ACTIVESHEET"? Is there any other command you might suggest to select the appropriate worksheet and NOT the worksheet where the macro is stored? "Dave Peterson" wrote: > I'd build a new test workbook and include that code that worked for me. > > Then test that. > > If it works, maybe it'll give you a hint what's going wrong. > > > > Eric_G wrote: > > > > Dave, > > > > All seems to be working BUT FOR the fact that the PDF file is actually > > saving a worksheet from the file in which the macro is stored AND NOT the > > data stored in DestinationFileName. The worksheets which are to be activated > > do not exist in the macro source file but the macro is correctly going to > > DestionationFileName to look for these worksheets and correctly selecting > > them. While the command " .Sheets("Investment Models E").Activate" is > > definitely included, the PDF file which is saved contains the worksheet which > > was active in the main file from which the macro is running. I'm stumped... > > > > "Dave Peterson" wrote: > > > > > Nothing pops out. > > > > > > You sure you're testing with xl2007, right? > > > > > > And you see an option in the SaveAs dialog for creating a PDF, right? > > > > > > Anyway, this worked ok for me in xl2007: > > > > > > Option Explicit > > > Sub testme() > > > > > > Dim wkbk As Workbook > > > Dim DestinationFileName As String > > > Dim xlFile_Drive As String > > > Dim temp_File_name As String > > > > > > DestinationFileName = "C:\My Documents\xl2007\book1.xlsx" > > > > > > xlFile_Drive = "C:\" > > > > > > temp_File_name = "test99.pdf" > > > > > > Set wkbk = Workbooks.Open(Filename:=DestinationFileName) > > > > > > With wkbk > > > .Activate 'it should already be active > > > .Sheets(Array("Investment Models E", "Open Models E")).Select > > > .Sheets("Investment Models E").Activate > > > End With > > > > > > ActiveSheet.ExportAsFixedFormat _ > > > Type:=xlTypePDF, _ > > > Filename:=xlFile_Drive & temp_File_name, _ > > > Quality:=xlQualityStandard, _ > > > IncludeDocProperties:=True, _ > > > IgnorePrintAreas:=False, _ > > > OpenAfterPublish:=False > > > > > > End Sub > > > > > > Eric_G wrote: > > > > > > > > thanks, but still having some problems. > > > > > > > > Can you see something inherently wrong with this code: > > > > > > > > Workbooks.Open Filename:=Destinationfile, UpdateLinks:=3 > > > > > > > > Workbooks("Keystone Performance-Apr-10.xlsx").Activate > > > > Sheets(Array("Investment Models E", "Open Models E")).Select > > > > Sheets("Investment Models E").Activate > > > > ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ > > > > xlfile_drive & temp_file_name _ > > > > , Quality:=xlQualityStandard, IncludeDocProperties:=True, > > > > IgnorePrintAreas _ > > > > :=False, OpenAfterPublish:=False > > > > > > > > ________________ > > > > "Dave Peterson" wrote: > > > > > > > > > It's not that. > > > > > > > > > > There are not many things (objects/ranges/sheets) that you have to select to > > > > > work on in VBA. > > > > > > > > > > But I loaded up xl2007 and this looks (to me, at least) that it's one of those > > > > > things. > > > > > > > > > > You could save the entire workbook > > > > > wkbk.exportasfixedwidth ... > > > > > but that would do all the sheets. > > > > > > > > > > or you could loop through the sheets that you want -- but that would result in > > > > > multiple PDF files. > > > > > > > > > > So ignore my post and go back to using the .select and activesheet stuff. > > > > > > > > > > > > > > > > > > > > Eric_G wrote: > > > > > > > > > > > > Thank you, Dave, for your prompt response. Unfortunately, I'm receiving an > > > > > > error at the line "wkbk.Sheets(Array(...)" -- the error is #438 -- "object > > > > > > doesn't support this property or method". > > > > > > > > > > > > Could this have something to do with the fact that the macro is being > > > > > > executed from file_1 yet the file which contains the worksheets "Investment > > > > > > Models E" and "Open Models E" is nvestment Models_F" is "destinationfile"??? > > > > > > > > > > > > Thanks. > > > > > > > > > > > > "Dave Peterson" wrote: > > > > > > > > > > > > > I would stay away from the Windows collection. If the user did a window|new > > > > > > > window, you'd see: > > > > > > > > > > > > > > book1.xls:1 > > > > > > > or > > > > > > > book1.xls:2 > > > > > > > > > > > > > > And that could screw up the .activate command. > > > > > > > > > > > > > > I wouldn't use this, but I bet destinationfile contains the drive, path and > > > > > > > filename. Unless you're doing something weird, you only want to supply the > > > > > > > filename: > > > > > > > > > > > > > > ========== > > > > > > > Anyway, I'd use a variable that represents that workbook. > > > > > > > > > > > > > > Dim wkbk as workbook > > > > > > > set wkbk = Workbooks.Open(Filename:=Destinationfile, UpdateLinks:=3) > > > > > > > wkbk.activate > > > > > > > > > > > > > > Now I don't need to worry about the name of the window -- or the name of the > > > > > > > file. > > > > > > > > > > > > > > ===== > > > > > > > I don't have xl2007 running, so I didn't test this and the compile failed on > > > > > > > some of the PDF settings, but you may want to try something like: > > > > > > > > > > > > > > Option Explicit > > > > > > > Sub testme() > > > > > > > > > > > > > > Dim wkbk As Workbook > > > > > > > Dim xlfile_drive As String > > > > > > > Dim Temp_File_Name As String > > > > > > > Dim DestinationFile As String > > > > > > > > > > > > > > DestinationFile = "C:\my documents\excel\book1.xls" > > > > > > > xlfile_drive = "C:\" > > > > > > > Temp_File_Name = "File_1.pdf" > > > > > > > > > > > > > > Set wkbk = Workbooks.Open(Filename:=DestinationFile, UpdateLinks:=3) > > > > > > > > > > > > > > wkbk.Sheets(Array("Investment Models E", "Open Models E")) _ > > > > > > > .ExportAsFixedFormat _ > > > > > > > Type:=xlTypePDF, _ > > > > > > > Filename:=xlfile_drive & Temp_File_Name, _ > > > > > > > Quality:=xlQualityStandard, _ > > > > > > > IncludeDocProperties:=True, _ > > > > > > > IgnorePrintAreas:=False, _ > > > > > > > OpenAfterPublish:=False > > > > > > > > > > > > > > > > > > > > > ==== > > > > > > > there's no selecting or activating. I think it makes the code easier to modify > > > > > > > and it may even make the routine run a bit faster (probably not noticeable -- > > > > > > > but the non-flickering will be noticeable!). > > > > > > > > > > > > > > Eric_G wrote: > > > > > > > > > > > > > > > > I am executing a macro from one worksheet and wish to perform actions on > > > > > > > > another. > > > > > > > > > > > > > > > > With the commands below, I am able to open the existing file called > > > > > > > > "destinationfile" which contains a number of worksheets. It's with the 2nd > > > > > > > > command line below where I get an error message; for some reason, I am unable > > > > > > > > to "ACTIVATE" the file which I just opened (i.e. destinationfile) in order to > > > > > > > > select specific worksheets (called "Investment Models E" and "Open Models E") > > > > > > > > and save them as a combined PDF document. > > > > > > > > > > > > > > > > NOTE that these commands are being executed from a master excel file (and > > > > > > > > not from the destinationfile itself). This is where I am having issues. > > > > > > > > > > > > > > > > Any assistance would be appreciated. > > > > > > > > > > > > > > > > Thanks. > > > > > > > > > > > > > > > > Workbooks.Open Filename:=Destinationfile, UpdateLinks:=3 > > > > > > > > > > > > > > > > Windows(destinationfile).Activate > > > > > > > > > > > > > > > > temp_file_name = "File_1.pdf" > > > > > > > > Sheets(Array("Investment Models E", "Open Models E")).Select > > > > > > > > Sheets("Investment Models E").Activate > > > > > > > > ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ > > > > > > > > xlfile_drive & temp_file_name, _ > > > > > > > > Quality:=xlQualityStandard, IncludeDocProperties:=True, > > > > > > > > IgnorePrintAreas _ > > > > > > > > :=False, OpenAfterPublish:=False > > > > > > > > > > > > > > -- > > > > > > > > > > > > > > Dave Peterson > > > > > > > . > > > > > > > > > > > > > > > > > -- > > > > > > > > > > Dave Peterson > > > > > . > > > > > > > > > > > -- > > > > > > Dave Peterson > > > . > > > > > -- > > Dave Peterson > . >
From: Dave Peterson on 7 May 2010 17:51
You could try: wkbk.ActiveSheet.ExportAsFixedFormat _ To see if that helps. Is there anything special about where the code is? Is it in a General module? Or about that workbook that you're opening? Is it hidden? Eric_G wrote: > > Thanks, I'll try that. > Is it possible that there's something wrong with the command "ACTIVESHEET"? > Is there any other command you might suggest to select the appropriate > worksheet and NOT the worksheet where the macro is stored? > > "Dave Peterson" wrote: > > > I'd build a new test workbook and include that code that worked for me. > > > > Then test that. > > > > If it works, maybe it'll give you a hint what's going wrong. > > > > > > > > Eric_G wrote: > > > > > > Dave, > > > > > > All seems to be working BUT FOR the fact that the PDF file is actually > > > saving a worksheet from the file in which the macro is stored AND NOT the > > > data stored in DestinationFileName. The worksheets which are to be activated > > > do not exist in the macro source file but the macro is correctly going to > > > DestionationFileName to look for these worksheets and correctly selecting > > > them. While the command " .Sheets("Investment Models E").Activate" is > > > definitely included, the PDF file which is saved contains the worksheet which > > > was active in the main file from which the macro is running. I'm stumped... > > > > > > "Dave Peterson" wrote: > > > > > > > Nothing pops out. > > > > > > > > You sure you're testing with xl2007, right? > > > > > > > > And you see an option in the SaveAs dialog for creating a PDF, right? > > > > > > > > Anyway, this worked ok for me in xl2007: > > > > > > > > Option Explicit > > > > Sub testme() > > > > > > > > Dim wkbk As Workbook > > > > Dim DestinationFileName As String > > > > Dim xlFile_Drive As String > > > > Dim temp_File_name As String > > > > > > > > DestinationFileName = "C:\My Documents\xl2007\book1.xlsx" > > > > > > > > xlFile_Drive = "C:\" > > > > > > > > temp_File_name = "test99.pdf" > > > > > > > > Set wkbk = Workbooks.Open(Filename:=DestinationFileName) > > > > > > > > With wkbk > > > > .Activate 'it should already be active > > > > .Sheets(Array("Investment Models E", "Open Models E")).Select > > > > .Sheets("Investment Models E").Activate > > > > End With > > > > > > > > ActiveSheet.ExportAsFixedFormat _ > > > > Type:=xlTypePDF, _ > > > > Filename:=xlFile_Drive & temp_File_name, _ > > > > Quality:=xlQualityStandard, _ > > > > IncludeDocProperties:=True, _ > > > > IgnorePrintAreas:=False, _ > > > > OpenAfterPublish:=False > > > > > > > > End Sub > > > > > > > > Eric_G wrote: > > > > > > > > > > thanks, but still having some problems. > > > > > > > > > > Can you see something inherently wrong with this code: > > > > > > > > > > Workbooks.Open Filename:=Destinationfile, UpdateLinks:=3 > > > > > > > > > > Workbooks("Keystone Performance-Apr-10.xlsx").Activate > > > > > Sheets(Array("Investment Models E", "Open Models E")).Select > > > > > Sheets("Investment Models E").Activate > > > > > ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ > > > > > xlfile_drive & temp_file_name _ > > > > > , Quality:=xlQualityStandard, IncludeDocProperties:=True, > > > > > IgnorePrintAreas _ > > > > > :=False, OpenAfterPublish:=False > > > > > > > > > > ________________ > > > > > "Dave Peterson" wrote: > > > > > > > > > > > It's not that. > > > > > > > > > > > > There are not many things (objects/ranges/sheets) that you have to select to > > > > > > work on in VBA. > > > > > > > > > > > > But I loaded up xl2007 and this looks (to me, at least) that it's one of those > > > > > > things. > > > > > > > > > > > > You could save the entire workbook > > > > > > wkbk.exportasfixedwidth ... > > > > > > but that would do all the sheets. > > > > > > > > > > > > or you could loop through the sheets that you want -- but that would result in > > > > > > multiple PDF files. > > > > > > > > > > > > So ignore my post and go back to using the .select and activesheet stuff. > > > > > > > > > > > > > > > > > > > > > > > > Eric_G wrote: > > > > > > > > > > > > > > Thank you, Dave, for your prompt response. Unfortunately, I'm receiving an > > > > > > > error at the line "wkbk.Sheets(Array(...)" -- the error is #438 -- "object > > > > > > > doesn't support this property or method". > > > > > > > > > > > > > > Could this have something to do with the fact that the macro is being > > > > > > > executed from file_1 yet the file which contains the worksheets "Investment > > > > > > > Models E" and "Open Models E" is nvestment Models_F" is "destinationfile"??? > > > > > > > > > > > > > > Thanks. > > > > > > > > > > > > > > "Dave Peterson" wrote: > > > > > > > > > > > > > > > I would stay away from the Windows collection. If the user did a window|new > > > > > > > > window, you'd see: > > > > > > > > > > > > > > > > book1.xls:1 > > > > > > > > or > > > > > > > > book1.xls:2 > > > > > > > > > > > > > > > > And that could screw up the .activate command. > > > > > > > > > > > > > > > > I wouldn't use this, but I bet destinationfile contains the drive, path and > > > > > > > > filename. Unless you're doing something weird, you only want to supply the > > > > > > > > filename: > > > > > > > > > > > > > > > > ========== > > > > > > > > Anyway, I'd use a variable that represents that workbook. > > > > > > > > > > > > > > > > Dim wkbk as workbook > > > > > > > > set wkbk = Workbooks.Open(Filename:=Destinationfile, UpdateLinks:=3) > > > > > > > > wkbk.activate > > > > > > > > > > > > > > > > Now I don't need to worry about the name of the window -- or the name of the > > > > > > > > file. > > > > > > > > > > > > > > > > ===== > > > > > > > > I don't have xl2007 running, so I didn't test this and the compile failed on > > > > > > > > some of the PDF settings, but you may want to try something like: > > > > > > > > > > > > > > > > Option Explicit > > > > > > > > Sub testme() > > > > > > > > > > > > > > > > Dim wkbk As Workbook > > > > > > > > Dim xlfile_drive As String > > > > > > > > Dim Temp_File_Name As String > > > > > > > > Dim DestinationFile As String > > > > > > > > > > > > > > > > DestinationFile = "C:\my documents\excel\book1.xls" > > > > > > > > xlfile_drive = "C:\" > > > > > > > > Temp_File_Name = "File_1.pdf" > > > > > > > > > > > > > > > > Set wkbk = Workbooks.Open(Filename:=DestinationFile, UpdateLinks:=3) > > > > > > > > > > > > > > > > wkbk.Sheets(Array("Investment Models E", "Open Models E")) _ > > > > > > > > .ExportAsFixedFormat _ > > > > > > > > Type:=xlTypePDF, _ > > > > > > > > Filename:=xlfile_drive & Temp_File_Name, _ > > > > > > > > Quality:=xlQualityStandard, _ > > > > > > > > IncludeDocProperties:=True, _ > > > > > > > > IgnorePrintAreas:=False, _ > > > > > > > > OpenAfterPublish:=False > > > > > > > > > > > > > > > > > > > > > > > > ==== > > > > > > > > there's no selecting or activating. I think it makes the code easier to modify > > > > > > > > and it may even make the routine run a bit faster (probably not noticeable -- > > > > > > > > but the non-flickering will be noticeable!). > > > > > > > > > > > > > > > > Eric_G wrote: > > > > > > > > > > > > > > > > > > I am executing a macro from one worksheet and wish to perform actions on > > > > > > > > > another. > > > > > > > > > > > > > > > > > > With the commands below, I am able to open the existing file called > > > > > > > > > "destinationfile" which contains a number of worksheets. It's with the 2nd > > > > > > > > > command line below where I get an error message; for some reason, I am unable > > > > > > > > > to "ACTIVATE" the file which I just opened (i.e. destinationfile) in order to > > > > > > > > > select specific worksheets (called "Investment Models E" and "Open Models E") > > > > > > > > > and save them as a combined PDF document. > > > > > > > > > > > > > > > > > > NOTE that these commands are being executed from a master excel file (and > > > > > > > > > not from the destinationfile itself). This is where I am having issues. > > > > > > > > > > > > > > > > > > Any assistance would be appreciated. > > > > > > > > > > > > > > > > > > Thanks. > > > > > > > > > > > > > > > > > > Workbooks.Open Filename:=Destinationfile, UpdateLinks:=3 > > > > > > > > > > > > > > > > > > Windows(destinationfile).Activate > > > > > > > > > > > > > > > > > > temp_file_name = "File_1.pdf" > > > > > > > > > Sheets(Array("Investment Models E", "Open Models E")).Select > > > > > > > > > Sheets("Investment Models E").Activate > > > > > > > > > ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ > > > > > > > > > xlfile_drive & temp_file_name, _ > > > > > > > > > Quality:=xlQualityStandard, IncludeDocProperties:=True, > > > > > > > > > IgnorePrintAreas _ > > > > > > > > > :=False, OpenAfterPublish:=False > > > > > > > > > > > > > > > > -- > > > > > > > > > > > > > > > > Dave Peterson > > > > > > > > . > > > > > > > > > > > > > > > > > > > > -- > > > > > > > > > > > > Dave Peterson > > > > > > . > > > > > > > > > > > > > > -- > > > > > > > > Dave Peterson > > > > . > > > > > > > > -- > > > > Dave Peterson > > . > > -- Dave Peterson |