Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening file in Excel 2003 opens multipule instances of same file | Excel Discussion (Misc queries) | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
Error:Invalid File format,while opening an Excel Template file | Excel Programming |