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

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

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

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

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
.

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

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
.

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