Problems executing commands on one file from another
in [Programming]
|
From: Eric_G on 5 May 2010 13:25 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
From: Gary Brown on 5 May 2010 15:20 Your syntax is probably off. Example: WORKS: Windows("Test.xls").Activate DOES NOT WORK Windows("Test").Activate Windows("C:\TEMP\Test.xls").Activate -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "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
From: Eric_G on 5 May 2010 15:31 Thanks, Gary, but unfortunately, I triple checked the file names and the macro still bombed at the command line "Windows("FILE NAME ENTERED HERE.xlsx").ACTIVATE I even replace the placeholder and entered the exact file name WITHOUT reference to the diretory and it still bombed. "Gary Brown" wrote: > Your syntax is probably off. > > Example: > > WORKS: > Windows("Test.xls").Activate > > DOES NOT WORK > Windows("Test").Activate > Windows("C:\TEMP\Test.xls").Activate > > -- > Hope this helps. > If it does, please click the Yes button. > Thanks in advance for your feedback. > Gary Brown > > > > "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
From: Dave Peterson on 5 May 2010 16:02 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
From: Eric_G on 6 May 2010 10:23
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 > . > |