Run an Excel macro from a different Excel file (Office 2K SR-3)

Topic

New Reply

Anyone have a clue on how to code a macro to execute a subroutine in a different Excel file? I have the code to open the file just as pretty as you please, but I cannot figure out the syntax to get it to run a particular subroutine once the file is opened.

And while we’re at it, any thoughts on how to code Excel VBA to enable Word macros when opening a Word file? The Word file executes a macro upon opening, but without the ability to enable Word macros from inside the Excel module, someone has to babysit to click on the “Enable Macros…” dialog.

Thanks!

Reply | Quote

Replies

Try:

    Run "C:MyWorkbooksOtherWorkboo.xls!MySubroutine"

Reply | Quote

That’s it. Dang, that was simple. I feel like an idiot. One secret, though, is that you can’t have spaces in the file name. Thanks very much.

Reply | Quote

You can have spaces in the file name if you put the path and file name in single quotes inside the double quotes.

Reply | Quote

OPW,

With regard to the second question, unless the macro security is set to Low (which isn’t advisable), a Word document is going to give you the enable macros prompt when you open it.

However, a Word template, which is located in a template directory, can contain macros (including macros that run when a file opens), and you will not get an enable macros prompt in that case.

Gary

Reply | Quote

Not entirely true Gary.

It al depends on *how* the doc is opened.

If (in the XL VBE) you set a reference to the Word object lib, this code:

Sub test()
Dim oWd As Word.Application
Dim oWdDoc As Word.Document
Set oWd = New Word.Application
Set oWdDoc = oWd.Documents.Open(“c:dataxl utilstest.doc”)
oWdDoc.Close False
oWd.Quit
Set oWdDoc = Nothing
Set oWd = Nothing
End Sub

Will open the designated document in Word, enabling macro’s whichever security has been set in Word…

Reply | Quote

Cheers, Jan Karel – – didn’t think of automation…

Reply | Quote

Brilliant, Jan Karel. very, very much.

Reply | Quote

I think something is missing. I changed your code to a different path and file name, but it won’t open the file. The next line of code, “oWdDoc.Close (False)” gives me this error prompt: “Run-time error ‘462’: The remote server machine does not exist or is not available”

Here is what I am trying to do:
As you know, Excel can handle a finite number of records. In our case, it is equivalent to about 1000 pages of a text-file report generated by our ERP. Basically, the Word file in question opens a 4000- to 5000-page text-file report, and then creates 4 or 5 new 1000-page text files that Excel can handle. This all works just fine if someone is there to babysit the Excel macro, so that when it gets around to opening Word, that someone can click on the “Enable Macros” button. We want to be able to run this Excel macro without any intervention, which means that the Word file has to open with macros enabled.

If you have any thoughts on how I could accomplish this more simply, I would greatly appreciate it.

Thanks,

Reply | Quote

Sounds like the Word document closes itself before XL gets the chance to do so…

Why are you importing into Word in the first place?

Reply | Quote

In this case, I’m using Word as a text editor to break a 4000- to 5000-page document into 1000-page files to import into Excel. I couldn’t think of a better way, short of writing something in VB and then creating a COM add-in. But I’m not there yet, technically. I may have to be pretty quickly, I guess.

Thanks,

Reply | Quote

No need to use Word for that.

Use the Text Import wizard of Excel, Whilst recording a macro.

On the very first page of the wizard, you may specify the “Start import at Row” parameter.

I made up this:

Sub Macro1()
‘
‘ Macro1 Macro
‘ Macro recorded 08-05-2003 by jankarelp
‘

‘
Dim lStart As Long
For lStart = 0 To 10
Workbooks.OpenText Filename:=”C:DataLogpar.txt”, Origin:=xlWindows, _
StartRow:=10000 * lStart + 1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1))
Next
End Sub

Reply | Quote

That’s very clever, but it doesn’t work on massive text files. The maximum row you can start at is 32767, which is a few lines into the 521st page of these reports. With the number of rows in Excel limited to 65536, that means I can only import up to the first few lines of page 1503. The other almost 3000 pages of the report will end up ignored .

It’s a very good idea, though, and I really appreciate your time and effort .

Reply | Quote

If the textfile contains a counter column, you could filter on that.

Otherwise, you’d have to write a VBA text import function to get rid of the Word step.

See if the code on this page helps.

Reply | Quote

Well, thanks. It does help, but not in this particular case. That’s good code, there, and it’s a lot more efficient than what I have been writing. Nonetheless, I’m trying to import a text file with over 256,000 lines. To get around the 65,536 line limit on a spreadsheet page, I have to import the text file in 64,000-line chunks. The best way I’ve found so far to do that is to use Word to break the original text file into smaller, 1,000-page, 64,000-line files. I’ve written a macro in Word to do that. Then the Excel macro picks up again and imports each of those text files in turn, cleans them up (i.e., delete extraneous lines and unnecessary data), and consolidates this monster down to a single spreadsheet of useful information.

The best solution I’ve found so far, and it’s only the best because I haven’t learned enough about automation and COM add-ins yet, is to have my Excel macro open a Word file which contains an “On_Open” macro that breaks large text files into 1,000-page text files.

If I cannot find a way to automatically enable Word macros without changing the security level, then I’ll start exploring a COM add-in based on VB code.

Jan Karel, I really really appreciate all the time and thought you’ve given this. You are great and I’ve learned a lot of things in the past couple of days. But please don’t lose any sleep over this. I should probably learn how to do COM add-ins anyway. It would only increase my value, right?

Thanks,

Reply | Quote

[indent]

---

The best solution I’ve found so far, and it’s only the best because I haven’t learned enough about automation and COM add-ins yet, is to have my Excel macro open a Word file which contains an “On_Open” macro that breaks large text files into 1,000-page text files.

---

[/indent]

I already showed how to open a doc in a Word session using Automation. You don’t need an Auto_Open (or Document_Open) event macro to start the macro once the doc is opened. In Fact, all of your code that processes the Word doc can reside in XL. Just make sure you precede everything with the Word object qualifier.

If it is not too much, you might post your Word code here and we’ll try to change it so it can be used from within Excel.

Alternatively, you can have Excel call the subroutine in Word explicitly after the doc is opened, rather than depending on an Open event..

Reply | Quote

Ooooo, I like that second alternative. The Word code is about 400 lines long, including a couple of subroutines, so I probably should not post it. But I really like that calling the procedure once Word is opened. Can you tell me how to do that? You did tell me how to call an Excel procedure from another Excel file, and I’ve already incorporated that into 3 different spreadsheets. That little trick alone saves me about a half-hour or so each week. Very cool.

Jan Karel, I admire your perseverance. Thank you so much.

Reply | Quote

For example:

Sub test()
Dim oWd As Word.Application
Dim oWdDoc As Word.Document
Set oWd = New Word.Application
Set oWdDoc = oWd.Documents.Open(“c:datatest.doc”)
oWd.Visible = True
‘This code line rund the macro called test123 located in the document called test.doc:
oWd.Run “c:datatest!test123”
‘Only when the above call to the Word macro is done, the next lines will be executed!
oWdDoc.Close False
oWd.Quit
Set oWdDoc = Nothing
Set oWd = Nothing
End Sub

Reply | Quote

OMIGOD OMIGOD OMIGOD!!! IT WORKS!!!!! You are great!

Thanks you so, so much.

Reply | Quote

[indent]

---

IT WORKS

---

[/indent] Good. Thanks for letting me know.

Reply | Quote

For future reference, if the report file consists of rows of delimited or fixed-width data, consider using ADO to access the file. If the report is “formatted,” never mind.

Reply | Quote

There are only two ways to avoid the prompt ‘enable/disable macros’ when Excel opens (at least I can only think of two ways):

1. set security = low (not work on Excel 97 since it’s not an option)
2. buy a digital certificate and ‘sign’ the Excel doc with it. Then only the first time the Excel file runs does anyone have to check the ‘trust’ box and enable macros.

Since Word gets started first why not have Word write directly to Excel (as an object) instead of launching Excel and have it run its own macros? If the Excel macros are the problem (because you have to enable it) why not just write yet more VBA (Word VBA this time) to write to Excel directly?

Deb

Reply | Quote

Another one is a workaround:

– Have a tiny add-in that contains a workbook_open event that looks like this:

Private Sub Workbook_Open()
Application.OnTime now, “CheckForBook”
End Sub

And in a normal module:

Sub CheckForBook()
Dim oWorkbook As Workbook
For Each oWorkbook In Application.Workbooks
If oWorkbook.FullName = “C:DataLoadTheAutoBook.xls” Then
Workbooks.Open “C:DATATheBookThatContainsCode.xls”
End If
Next
End Sub

Save as an add-in and install the add-in.

Now load an empty workbook named “C:DataLoadTheAutoBook.xls”

Reply | Quote

Actually, Excel starts first and opens Word a few lines after an Excel macro is manually started. I could have Word run its macro to parse this massive file and then write to Excel, but then I’m faced with the problem of deleting all the normal unnecessary data from this report. I’d have to open Excel anyway and run *its* macros to consolidate multi-line records into a single-line record, which is the ultimate purpose of this exercise.

Reply | Quote

[indent]

---

2. buy a digital certificate and ‘sign’ the Excel doc with it. Then only the first time the Excel file runs does anyone have to check the ‘trust’ box and enable macros.

---

[/indent]Office has an optional utility, Selfcert, that can be installed and then used to create a certificate on your computer. This certificate can then be used to sign your projects. If this is for your own computer or other in-house systems then it may prove to be all you need.

Reply | Quote

Yes I’ve used SelfCert too but it’s not as secure as a ‘real’ certificate. If it’s for in-house use only then I’d say it’s ok but not for a real product that’s used by customers of product you plan on distributing.

Deb

Reply | Quote