Odd issue with an Excel worksheet using macros

M

mycroteck

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
 
J

Joel

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.
 
J

Jim Rech

PriceList = "O:\New Master\FAX_A.XLS"
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
|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
 
M

mycroteck

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?
 
M

mycroteck

OK... I will give it a try ... Thanks.

Jim Rech said:
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
|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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2003 created Workbook casuses error when macro runs in 2007 2
Simple Macro freezes the whole Excel and PC! 5
Button looses Macro setting when running Macro 1
MAJOR PROBLEM! --- Menu Bars don't unhide? 9
VBA Expected end of statement ERROR 3
Macros: Step Thru Works, Run Works, Keyboard Shortcut Locks up 2
Excel macro error when workbook is shared 0
Can't disable macros in 2nd Excel instance 0

Top