Running excel VBA Code from a pre 2003 Macro

Microsoft Excel 2003 (full product)
October 14, 2009 at 09:08:38
Specs: Windows Vista
Hi,

I have a macro file in the old format (pre2003) that does a lot of complicated automated things. Since then (with help) I have developed some VBA scripts to enhance it.

Recently a need arised to make a call from the MACRO file to run a VBA script, and the system is complaining. The VBA script is fine. If you run it as a stand alone works perfectly.

Here is the MACRO line calling it:

=RUN(!Module1.InvoiceToBatch)

The Macro file is in a separate worksheet which hold the worksheet having teh VBA script in Module 1

Basically, the process stops with an error on Macro line XX which has the following code:

=RUN(!Module1.InvoiceToBatch)

Can anyone point where this is falling down?

Many tahnks.

Tal√Ęt


See More: Running excel VBA Code from a pre 2003 Macro

Report •


#1
October 14, 2009 at 09:21:10
re: Basically, the process stops with an error...

Care to share what the error is?

Number and text please.


Report •

#2
October 19, 2009 at 08:40:25
The error message box simply says

Macro Error at cell:
[Salemac.xlm]salemac!A289

Halt Step Continue

Cell A289 has the macro instruction

=RUN(!Module1.InvoiceToBatch)



Report •

#3
October 19, 2009 at 08:45:12
I guess what I am trying to find is

What is the syntax if you want to call VBA code from an old Macro code? Can it be done? Does the syntax change if the pre 2003 macro file is in a separate file, or its in a work sheet in the same file which holds the other VBA Codes?

T.


Report •

Related Solutions

#4
October 19, 2009 at 09:08:49
Hi,
See my next post.
Regards

Report •

#5
October 19, 2009 at 10:36:28
Hi,

You need the location of the VBA module to be fully qualified and include () at the end.

For example I have a VBA macro in Excel 2003 named Ttest
It is in Module1 of the Workbook Personal.xls

In a macro sheet cell I have:
=CALL(PERSONAL.XLS!Module1.Ttest())

Running the Macro on the Macro sheet calls Ttest which in my case executes a message box.

HTH

Regards


Report •

#6
October 20, 2009 at 03:39:20
Thanks Humar.

Your suggestion works well. Teh macro now starts teh VBA script. However, A new problem has arisen, which I had not forseen.

The template excell file, INVSALE.XLS is used in a 2 stage process. Its the second stage where the StockstoBatch VBA is triggered by the pre2003 Macro code.

However, at the end of the first stage, the file name is changed from INVSALE.XLS , and the file is saved under the Invoice number, which will always be different. So the process of hard coding the location of the VBA module fully qualified is not possible because the file name changes for each numbered invoice. What does not change is the sheet name that holds the VBA if that is any help.... Unless there is a way to get the VBA code to modify itself by looking up the file name each time! ouch!

T.


Report •

#7
October 20, 2009 at 05:59:39
Hi,

Without seeing your code, I suggest that the following may work:
In the existing Macro sheet create a variable with the filename based on the invoice, and use it as the 'SaveAs' filename,
then
Pass this variable to a VBA subroutine, in a fixed location such as Personal.xls

This is my Macro code:

strFN="C:\Temp\Teach.xls"
=CALL(PERSONAL.XLS!Module1.Ttest(strFN))

(The filename 'Teach.xls' would be replaced by your Invoice-based filename)

The Ttest subroutine in PERSONAL.XLS!Module1.Ttest receives the new filename, modifies it and then calls the subroutine in the Invoice file.

Sub TTest(strM As String)
strNewName = "'" & Right(strM, 9) & "'!Ttest2"
Run strNewName
End Sub

strNewName looked like this 'Teach.xls'!Ttest2, enclosed in double quotes.
My manipulation of the filename with RIGHT() is crude and can be improved, but in any case will require a change to match your invoiced-based filenames.

There may be a way to do this by changing the CALL() in the Macro sheet, but I couldn't get it to work - it is many years since I used the Macrosheet macros.

So in summary a two step process.
Pass the invoice based filename to a fixed location VBA subroutine.
The VBA subroutine does the manipulation on the filename and calls the subroutine in the changing invoice-based workbook.

HTH

Regards


Report •

#8
October 21, 2009 at 04:59:18
Thanks Humar.

I don't really understand when you say:

"In the existing Macro sheet create a variable with the filename based on the invoice, and use it as the 'SaveAs' filename,
then
Pass this variable to a VBA subroutine, in a fixed location such as Personal.xls"

Currently, I pick up teh template invoice file names INVSALE.XLS Run a series of macros to pick up teh next Invoice number, populate it with products, quantities, print a packing sheet and write to a log file. This file is then saved as a new file using the invoice number. So INVSALE.XLS beccomes 10058.XLS. Same for teh next invoice, and so on 10059.XLS, 10060.XLS ....

In the second stage, and this is where the VBA code is called, I pick up the file 10059.XLS add in posting details and postage costs, use a series of macros to write to other log files, and print final invoice copies. Part of thsi process calls a VB code from teh Macro sheet. (We sorted this problem now and teh Macro CALLS teh VB rather than RUNs. However, the macro directs teh call to a VB that references the template file INVSALE.XLS.

I have not understood the first part of your instructions how I will overcome this constantly changing location reference.

I have not got past this point to be able to say anything about the following stages you gave me... Sorry amy be I am being a bit thick here.

T.


Report •

#9
October 21, 2009 at 06:00:21
Hi,

Sorry for not being clear.

OK, so you have said that you do this:
So INVSALE.XLS beccomes 10058.XLS. Same for the next invoice, and so on 10059.XLS, 10060.XLS.

This means that in your macros you create new filenames such as 10058.XLS.
Now add a new macro step which assigns this new name to a variable, for example strFN=
I don't know how you create these new file names, but if you can create them, then you can assign them to a variable (strFN) with an = ,
strFN = <the next file name>

Then when you call your Visual Basic Macro include the variable strFN between the brackets in the call command.
=CALL(PERSONAL.XLS!Module1.Ttest(strFN))

Perhaps posting the code you use to create the new filenames would help.

Regards


Report •

#10
October 21, 2009 at 06:22:43
Thanks.

This means that in your macros you create new filenames such as 10058.XLS.

This is done manually. After the template (INVSALE.XLS) with new invoice number and details have been populated, I just do a manual "save as" and save the file under the new file name which is the invoice number. I suppose I can try and adapt it so that this is also done automatically as part of a MACRO or VB code. I will leave that for later. I tend to do improvements in small manageable chunks and as the need arise. This whole appliaction has been growing and getting bit more sophisticated over many years; hence the changeover from MACRO sheets to VB codes, but still retaining the old macros.

T.


Report •

#11
October 21, 2009 at 07:50:11
Hi,

I didn't realize that you were doing the renaming manually - hence the confusion.

How about an Input dialog box to get the new name.

In Visual basic in INVOICE.xls in Module1 I have the following:

Sub SaveNew()
Dim strFN As String
Dim strCodeName As String

'get new filename to save Invoice file as
strFN = InputBox("Enter new filename - no need to include .xls", "Invoice Filenames")
If strFN <> "" Then
    If Right(strFN, 3) <> "xls" Then
        strFN = strFN & ".xls"
    End If

    'save the invoice file with the new name
    Workbooks("Invoice.xls").SaveAs strFN

    'run code in renamed Invoice workbook
    strCodeName = "'" & strFN & "'!Ttest2"
    Run strCodeName
End If
End Sub

Sub Ttest2()
MsgBox ActiveWorkbook.Name
End Sub

This allows you to enter the new filename, and then it saves the INVOICE.xls with the new name and then calls a Macro in the new workbook.

As an example the macro Ttest2 just puts a message on the screen with its own name.

Regards


Report •


Ask Question