Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd issue with an Excel worksheet using macros
I am trying to fix a problem with a workbook and macros within the workbook.
Here is the issue: I open a workbook that contains macros in another workbook. The files are located on a server under a mapped drive in windows. The workbook is a .xls not xlsx. I run a macro that creates a fax and faxes it. The first time I run it, it faxes some of the workbook fine but if I attempt to run the same macro again I get the following error: Run-time error 1004 Fax_A.xls could not be found. Check the spelling of the name. From trouble shooting I have determined that excel does not know the location of the specified workbook. So if I click on the explorer icon in excel, and go to the folder the workbook is located in, it runs fine. Then if I try another time, I get the same error. When I open the explorer icon in excel, it is looking at the "Office12" folder instead of the folder that the workbooks are located in. I have changed the default file location in excel to point to the folder that contains the files but it still defaults back to "office12" one the macro is ran and the file is faxed. Here is a snippet of the code in the macro: '--------------------------------------------------------- ' SendFax Macros ' Macro recorded 6/30/97 by ' '--------------------------------------------------------- Sub SendFax_A() PriceList = "FAX_A.XLS" Application.Run Macro:="MASTER.XLS!Send_a_Fax" End Sub Sub Send_a_Fax() Workbooks.Open Filename:=PriceList, UpdateLinks:=1 Windows(PriceList).Activate ActiveWindow.Visible = True 'MsgBox Application.ActivePrinter Application.ActivePrinter = TheFAX On Error Resume Next ActiveWindow.SelectedSheets.PrintOut Copies:=1 ' AppActivate "Delrina WinFax PRO" ' SendKeys "%S", True 'Send ' SendKeys "F", True 'Fax ' SendKeys "%T", True 'To: ' SendKeys "Test", True ' SendKeys "%G", True 'Group ' SendKeys "%L", True 'Add to List DoEvents ' Windows(PriceList).Activate ActiveWorkbook.Save ActiveWorkbook.Close (False) End Sub '--------------------------------------------------------- ' Auto_Close Macro ' Macro recorded 7/7/97 '--------------------------------------------------------- Sub Auto_Close() Windows("MASTER.XLS").Activate ActiveWorkbook.Save ActiveWorkbook.Close End Sub '--------------------------------------------------------- ' WIP Macro ' Macro recorded 7/8/97 ' '--------------------------------------------------------- Sub WIP() MsgBox "This button is not implemented, please press OK." End Sub ' When I place the path of the file in the routine like this: Sub SendFax_A() PriceList = "O:\New Master\FAX_A.XLS" Application.Run Macro:="MASTER.XLS!Send_a_Fax" End Sub Sub Send_a_Fax() Workbooks.Open Filename:=PriceList, UpdateLinks:=1 Windows(PriceList).Activate ActiveWindow.Visible = True I receive the following error: Run-time error '9': Subscript out of Range" Any thoughts? Thanks in advance for all your help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd issue with an Excel worksheet using macros
first, when you openb a new workbook it automaticaly becomes the active
workbook. Normally I do the following workbooks.open filename:=abc.xls set newbk = activeworkbook then later in code with newbk or newbk.sheets("Sheet1").Range("A1") the problem you are having is the windows function only wants the book name note the entire path you have Sub SendFax_A() PriceList = "O:\New Master\FAX_A.XLS" Application.Run Macro:="MASTER.XLS!Send_a_Fax" End Sub Price list contains the path so it fails in the following code Sub Send_a_Fax() Workbooks.Open Filename:=PriceList, UpdateLinks:=1 Windows(PriceList).Activate ActiveWindow.Visible = True You really don't need the Windows statement because the book is already active when you open it. "mycroteck" wrote: I am trying to fix a problem with a workbook and macros within the workbook. Here is the issue: I open a workbook that contains macros in another workbook. The files are located on a server under a mapped drive in windows. The workbook is a .xls not xlsx. I run a macro that creates a fax and faxes it. The first time I run it, it faxes some of the workbook fine but if I attempt to run the same macro again I get the following error: Run-time error 1004 Fax_A.xls could not be found. Check the spelling of the name. From trouble shooting I have determined that excel does not know the location of the specified workbook. So if I click on the explorer icon in excel, and go to the folder the workbook is located in, it runs fine. Then if I try another time, I get the same error. When I open the explorer icon in excel, it is looking at the "Office12" folder instead of the folder that the workbooks are located in. I have changed the default file location in excel to point to the folder that contains the files but it still defaults back to "office12" one the macro is ran and the file is faxed. Here is a snippet of the code in the macro: '--------------------------------------------------------- ' SendFax Macros ' Macro recorded 6/30/97 by ' '--------------------------------------------------------- Sub SendFax_A() PriceList = "FAX_A.XLS" Application.Run Macro:="MASTER.XLS!Send_a_Fax" End Sub Sub Send_a_Fax() Workbooks.Open Filename:=PriceList, UpdateLinks:=1 Windows(PriceList).Activate ActiveWindow.Visible = True 'MsgBox Application.ActivePrinter Application.ActivePrinter = TheFAX On Error Resume Next ActiveWindow.SelectedSheets.PrintOut Copies:=1 ' AppActivate "Delrina WinFax PRO" ' SendKeys "%S", True 'Send ' SendKeys "F", True 'Fax ' SendKeys "%T", True 'To: ' SendKeys "Test", True ' SendKeys "%G", True 'Group ' SendKeys "%L", True 'Add to List DoEvents ' Windows(PriceList).Activate ActiveWorkbook.Save ActiveWorkbook.Close (False) End Sub '--------------------------------------------------------- ' Auto_Close Macro ' Macro recorded 7/7/97 '--------------------------------------------------------- Sub Auto_Close() Windows("MASTER.XLS").Activate ActiveWorkbook.Save ActiveWorkbook.Close End Sub '--------------------------------------------------------- ' WIP Macro ' Macro recorded 7/8/97 ' '--------------------------------------------------------- Sub WIP() MsgBox "This button is not implemented, please press OK." End Sub ' When I place the path of the file in the routine like this: Sub SendFax_A() PriceList = "O:\New Master\FAX_A.XLS" Application.Run Macro:="MASTER.XLS!Send_a_Fax" End Sub Sub Send_a_Fax() Workbooks.Open Filename:=PriceList, UpdateLinks:=1 Windows(PriceList).Activate ActiveWindow.Visible = True I receive the following error: Run-time error '9': Subscript out of Range" Any thoughts? Thanks in advance for all your help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd issue with an Excel worksheet using macros
PriceList = "O:\New Master\FAX_A.XLS"
Windows(PriceList).Activate The window name is whatever appears in the window's caption bar. It never includes the path and may or may not include the extension depending on your MS Windows preference re showing file extensions. -- Jim "mycroteck" wrote in message ... |I am trying to fix a problem with a workbook and macros within the workbook. | Here is the issue: | I open a workbook that contains macros in another workbook. The files are | located on a server under a mapped drive in windows. The workbook is a ..xls | not xlsx. I run a macro that creates a fax and faxes it. The first time I | run it, it faxes some of the workbook fine but if I attempt to run the same | macro again I get the following error: | | Run-time error 1004 | Fax_A.xls could not be found. Check the spelling of the name. | | From trouble shooting I have determined that excel does not know the | location of the specified workbook. So if I click on the explorer icon in | excel, and go to the folder the workbook is located in, it runs fine. Then | if I try another time, I get the same error. | | When I open the explorer icon in excel, it is looking at the "Office12" | folder instead of the folder that the workbooks are located in. | I have changed the default file location in excel to point to the folder | that contains the files but it still defaults back to "office12" one the | macro is ran and the file is faxed. | | Here is a snippet of the code in the macro: | | '--------------------------------------------------------- | ' SendFax Macros | ' Macro recorded 6/30/97 by | ' | '--------------------------------------------------------- | Sub SendFax_A() | PriceList = "FAX_A.XLS" | Application.Run Macro:="MASTER.XLS!Send_a_Fax" | End Sub | Sub Send_a_Fax() | Workbooks.Open Filename:=PriceList, UpdateLinks:=1 | Windows(PriceList).Activate | ActiveWindow.Visible = True | | 'MsgBox Application.ActivePrinter | Application.ActivePrinter = TheFAX | On Error Resume Next | ActiveWindow.SelectedSheets.PrintOut Copies:=1 | ' AppActivate "Delrina WinFax PRO" | ' SendKeys "%S", True 'Send | ' SendKeys "F", True 'Fax | ' SendKeys "%T", True 'To: | ' SendKeys "Test", True | ' SendKeys "%G", True 'Group | ' SendKeys "%L", True 'Add to List | DoEvents | ' | Windows(PriceList).Activate | ActiveWorkbook.Save | ActiveWorkbook.Close (False) | End Sub | '--------------------------------------------------------- | ' Auto_Close Macro | ' Macro recorded 7/7/97 | '--------------------------------------------------------- | Sub Auto_Close() | Windows("MASTER.XLS").Activate | ActiveWorkbook.Save | ActiveWorkbook.Close | End Sub | '--------------------------------------------------------- | ' WIP Macro | ' Macro recorded 7/8/97 | ' | '--------------------------------------------------------- | Sub WIP() | MsgBox "This button is not implemented, please press OK." | End Sub | ' | | | When I place the path of the file in the routine like this: | | Sub SendFax_A() | PriceList = "O:\New Master\FAX_A.XLS" | Application.Run Macro:="MASTER.XLS!Send_a_Fax" | End Sub | | Sub Send_a_Fax() | Workbooks.Open Filename:=PriceList, UpdateLinks:=1 | Windows(PriceList).Activate | ActiveWindow.Visible = True | | I receive the following error: | Run-time error '9': | Subscript out of Range" | | Any thoughts? | | Thanks in advance for all your help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd issue with an Excel worksheet using macros
Ok. If I do not include the path We receive the first error
Run-time error 1004 Fax_A.xls could not be found. Check the spelling of the name. More information that might help understand our problem. There are about 15 other spreadsheets that are included in this script. I posted only a portion bacause I thought that if we could fix this routine I could apply it to the others and we wold be good. Each routine is identical save for the file.xls. Every time we open the location that the master worksheet resides in, it will work and finish the routine. Once the routine is finished and we go to click on the next portion to run, it throws a debug error and displays the error 1004. We then click on the folder location and it reverts back to office12 and not the location of the master spreadsheet. If we repeat the steps we can eventually get through the entire process. I can try to take out the "Windows(PriceList).Activate " and see what happens... I am not sure if it is a problem with the macro or with excel 2007? "Joel" wrote: first, when you openb a new workbook it automaticaly becomes the active workbook. Normally I do the following workbooks.open filename:=abc.xls set newbk = activeworkbook then later in code with newbk or newbk.sheets("Sheet1").Range("A1") the problem you are having is the windows function only wants the book name note the entire path you have Sub SendFax_A() PriceList = "O:\New Master\FAX_A.XLS" Application.Run Macro:="MASTER.XLS!Send_a_Fax" End Sub Price list contains the path so it fails in the following code Sub Send_a_Fax() Workbooks.Open Filename:=PriceList, UpdateLinks:=1 Windows(PriceList).Activate ActiveWindow.Visible = True You really don't need the Windows statement because the book is already active when you open it. "mycroteck" wrote: I am trying to fix a problem with a workbook and macros within the workbook. Here is the issue: I open a workbook that contains macros in another workbook. The files are located on a server under a mapped drive in windows. The workbook is a .xls not xlsx. I run a macro that creates a fax and faxes it. The first time I run it, it faxes some of the workbook fine but if I attempt to run the same macro again I get the following error: Run-time error 1004 Fax_A.xls could not be found. Check the spelling of the name. From trouble shooting I have determined that excel does not know the location of the specified workbook. So if I click on the explorer icon in excel, and go to the folder the workbook is located in, it runs fine. Then if I try another time, I get the same error. When I open the explorer icon in excel, it is looking at the "Office12" folder instead of the folder that the workbooks are located in. I have changed the default file location in excel to point to the folder that contains the files but it still defaults back to "office12" one the macro is ran and the file is faxed. Here is a snippet of the code in the macro: '--------------------------------------------------------- ' SendFax Macros ' Macro recorded 6/30/97 by ' '--------------------------------------------------------- Sub SendFax_A() PriceList = "FAX_A.XLS" Application.Run Macro:="MASTER.XLS!Send_a_Fax" End Sub Sub Send_a_Fax() Workbooks.Open Filename:=PriceList, UpdateLinks:=1 Windows(PriceList).Activate ActiveWindow.Visible = True 'MsgBox Application.ActivePrinter Application.ActivePrinter = TheFAX On Error Resume Next ActiveWindow.SelectedSheets.PrintOut Copies:=1 ' AppActivate "Delrina WinFax PRO" ' SendKeys "%S", True 'Send ' SendKeys "F", True 'Fax ' SendKeys "%T", True 'To: ' SendKeys "Test", True ' SendKeys "%G", True 'Group ' SendKeys "%L", True 'Add to List DoEvents ' Windows(PriceList).Activate ActiveWorkbook.Save ActiveWorkbook.Close (False) End Sub '--------------------------------------------------------- ' Auto_Close Macro ' Macro recorded 7/7/97 '--------------------------------------------------------- Sub Auto_Close() Windows("MASTER.XLS").Activate ActiveWorkbook.Save ActiveWorkbook.Close End Sub '--------------------------------------------------------- ' WIP Macro ' Macro recorded 7/8/97 ' '--------------------------------------------------------- Sub WIP() MsgBox "This button is not implemented, please press OK." End Sub ' When I place the path of the file in the routine like this: Sub SendFax_A() PriceList = "O:\New Master\FAX_A.XLS" Application.Run Macro:="MASTER.XLS!Send_a_Fax" End Sub Sub Send_a_Fax() Workbooks.Open Filename:=PriceList, UpdateLinks:=1 Windows(PriceList).Activate ActiveWindow.Visible = True I receive the following error: Run-time error '9': Subscript out of Range" Any thoughts? Thanks in advance for all your help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd issue with an Excel worksheet using macros
OK... I will give it a try ... Thanks.
"Jim Rech" wrote: PriceList = "O:\New Master\FAX_A.XLS" Windows(PriceList).Activate The window name is whatever appears in the window's caption bar. It never includes the path and may or may not include the extension depending on your MS Windows preference re showing file extensions. -- Jim "mycroteck" wrote in message ... |I am trying to fix a problem with a workbook and macros within the workbook. | Here is the issue: | I open a workbook that contains macros in another workbook. The files are | located on a server under a mapped drive in windows. The workbook is a ..xls | not xlsx. I run a macro that creates a fax and faxes it. The first time I | run it, it faxes some of the workbook fine but if I attempt to run the same | macro again I get the following error: | | Run-time error 1004 | Fax_A.xls could not be found. Check the spelling of the name. | | From trouble shooting I have determined that excel does not know the | location of the specified workbook. So if I click on the explorer icon in | excel, and go to the folder the workbook is located in, it runs fine. Then | if I try another time, I get the same error. | | When I open the explorer icon in excel, it is looking at the "Office12" | folder instead of the folder that the workbooks are located in. | I have changed the default file location in excel to point to the folder | that contains the files but it still defaults back to "office12" one the | macro is ran and the file is faxed. | | Here is a snippet of the code in the macro: | | '--------------------------------------------------------- | ' SendFax Macros | ' Macro recorded 6/30/97 by | ' | '--------------------------------------------------------- | Sub SendFax_A() | PriceList = "FAX_A.XLS" | Application.Run Macro:="MASTER.XLS!Send_a_Fax" | End Sub | Sub Send_a_Fax() | Workbooks.Open Filename:=PriceList, UpdateLinks:=1 | Windows(PriceList).Activate | ActiveWindow.Visible = True | | 'MsgBox Application.ActivePrinter | Application.ActivePrinter = TheFAX | On Error Resume Next | ActiveWindow.SelectedSheets.PrintOut Copies:=1 | ' AppActivate "Delrina WinFax PRO" | ' SendKeys "%S", True 'Send | ' SendKeys "F", True 'Fax | ' SendKeys "%T", True 'To: | ' SendKeys "Test", True | ' SendKeys "%G", True 'Group | ' SendKeys "%L", True 'Add to List | DoEvents | ' | Windows(PriceList).Activate | ActiveWorkbook.Save | ActiveWorkbook.Close (False) | End Sub | '--------------------------------------------------------- | ' Auto_Close Macro | ' Macro recorded 7/7/97 | '--------------------------------------------------------- | Sub Auto_Close() | Windows("MASTER.XLS").Activate | ActiveWorkbook.Save | ActiveWorkbook.Close | End Sub | '--------------------------------------------------------- | ' WIP Macro | ' Macro recorded 7/8/97 | ' | '--------------------------------------------------------- | Sub WIP() | MsgBox "This button is not implemented, please press OK." | End Sub | ' | | | When I place the path of the file in the routine like this: | | Sub SendFax_A() | PriceList = "O:\New Master\FAX_A.XLS" | Application.Run Macro:="MASTER.XLS!Send_a_Fax" | End Sub | | Sub Send_a_Fax() | Workbooks.Open Filename:=PriceList, UpdateLinks:=1 | Windows(PriceList).Activate | ActiveWindow.Visible = True | | I receive the following error: | Run-time error '9': | Subscript out of Range" | | Any thoughts? | | Thanks in advance for all your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros Issue | Excel Discussion (Misc queries) | |||
Excel Worksheet Password Issue!!!!! | Excel Discussion (Misc queries) | |||
VB macros issue with selecting cells on different page | Excel Discussion (Misc queries) | |||
Macros in Excel Worksheet | Excel Discussion (Misc queries) | |||
Debug issue with macros | Excel Programming |