Odd issue with an Excel worksheet using macros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mycroteck - 27 Mar 2008 21:47 GMT

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 to this Message

Joel - 27 Mar 2008 22:01 GMT

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.

> I am trying to fix a problem with a workbook and macros within the workbook.
> Here is the issue:
[quoted text clipped - 89 lines]
>
> Thanks in advance for all your help

Reply to this Message

mycroteck - 27 Mar 2008 22:19 GMT

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?

> first, when you openb a new workbook it automaticaly becomes the active
> workbook. Normally I do the following
[quoted text clipped - 123 lines]
> >
> > Thanks in advance for all your help

Reply to this Message

Jim Rech - 27 Mar 2008 22:12 GMT

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

Signature

Jim

|I am trying to fix a problem with a workbook and macros within the workbook.
| Here is the issue:
[quoted text clipped - 89 lines]
|
| Thanks in advance for all your help

Reply to this Message

mycroteck - 27 Mar 2008 22:29 GMT

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

> >> PriceList = "O:\New Master\FAX_A.XLS"
> >>Windows(PriceList).Activate
[quoted text clipped - 101 lines]
> |
> | Thanks in advance for all your help

Reply to this Message