Use a xla file to look in xls file when opening
 

Hello!

I am trying to write an xla-file that runs a macro when a xls-file is
opening (this is actually my first xla-file)
Then it should look in the xls sheet and if the cells(1,1) is "L" then it
should format the xls sheet page setup.

The problem is that the xls filename and sheetname isn't known. And I can't
make it to work to look in cells in the xls-file.
Here is the code in the xla-file (works if I add it to an xls-file):

Private Sub Workbook_Open()
If Cells(1, 1) = "Orientation L=Landscape" And Cells(2, 1) = "Fit A4 width
TRUE=Fit" Then
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
If Cells(1, 2) = "L" Then .Orientation = xlLandscape
.Zoom = False
If Cells(2, 2) = "TRUE" Then .FitToPagesWide = 1: .FitToPagesTall =
100
End With
End If
End Sub

The error message I get when I opens the xls-file is:
Run-time error '1004':
Method 'Cells' of object '_Global' failed

It seems like the workbook (xls) isn't available or in focus when the code
is running.
Could anyone help me to solve this?
Regards Magnus

Use a xla file to look in xls file when opening
 

The workbook_open event is for stuff you want to happen when you open that
workbook--not other workbooks.

If you want to control what happens when excel opens any old workbook, you'll
need an application event (not just a workbook event).

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

Magnus wrote:

Hello!

I am trying to write an xla-file that runs a macro when a xls-file is
opening (this is actually my first xla-file)
Then it should look in the xls sheet and if the cells(1,1) is "L" then it
should format the xls sheet page setup.

The problem is that the xls filename and sheetname isn't known. And I can't
make it to work to look in cells in the xls-file.
Here is the code in the xla-file (works if I add it to an xls-file):

Private Sub Workbook_Open()
If Cells(1, 1) = "Orientation L=Landscape" And Cells(2, 1) = "Fit A4 width
TRUE=Fit" Then
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
If Cells(1, 2) = "L" Then .Orientation = xlLandscape
.Zoom = False
If Cells(2, 2) = "TRUE" Then .FitToPagesWide = 1: .FitToPagesTall =
100
End With
End If
End Sub

The error message I get when I opens the xls-file is:
Run-time error '1004':
Method 'Cells' of object '_Global' failed

It seems like the workbook (xls) isn't available or in focus when the code
is running.
Could anyone help me to solve this?
Regards Magnus

--

Dave Peterson

Use a xla file to look in xls file when opening
 

Thank you very much. It worked perfectly!!!

Best regards
/Magnus

"Dave Peterson" wrote in message
...
The workbook_open event is for stuff you want to happen when you open that
workbook--not other workbooks.

If you want to control what happens when excel opens any old workbook,
you'll
need an application event (not just a workbook event).

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

Magnus wrote:

Hello!

I am trying to write an xla-file that runs a macro when a xls-file is
opening (this is actually my first xla-file)
Then it should look in the xls sheet and if the cells(1,1) is "L" then it
should format the xls sheet page setup.

The problem is that the xls filename and sheetname isn't known. And I
can't
make it to work to look in cells in the xls-file.
Here is the code in the xla-file (works if I add it to an xls-file):

Private Sub Workbook_Open()
If Cells(1, 1) = "Orientation L=Landscape" And Cells(2, 1) = "Fit A4
width
TRUE=Fit" Then
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
If Cells(1, 2) = "L" Then .Orientation = xlLandscape
.Zoom = False
If Cells(2, 2) = "TRUE" Then .FitToPagesWide = 1: .FitToPagesTall =
100
End With
End If
End Sub

The error message I get when I opens the xls-file is:
Run-time error '1004':
Method 'Cells' of object '_Global' failed

It seems like the workbook (xls) isn't available or in focus when the
code
is running.
Could anyone help me to solve this?
Regards Magnus

--

Dave Peterson