Problems executing commands on one file from another

[Eric_G]

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

 

[Gary Brown]

Your syntax is probably off.

Example:

WORKS:
Windows("Test.xls").Activate

DOES NOT WORK
Windows("Test").Activate
Windows("C:\TEMP\Test.xls").Activate

 

[Eric_G]

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.

 

[Dave Peterson]

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]

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]

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]

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]

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]

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]

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]

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]

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?