Running a Global Macro in Excel

[Vilmondes]

Hello,

I've created a global Excel macro to have at available at all times.

When you created a global macro, its name is PERSONAL.XLSB!MacroName.

I used the below code, but it won't run the Macro:

Code: Select all

Let>csvFile=c:\temp\streams\toyota-export.csv
Let>csvNewFile=c:\temp\streams\toyota-export-modified.csv

XLOpen>csvFile,1,xlH
XLRun>xlH,PERSONAL.XLSB!CRLF_Removal
XLSave>xlH,csvNewFile
XLQuit>xlH

Wondering if the XLRun works with global macros.

This is the article I followed to create that Excel macro:
http://blog.johnmuellerbooks.com/2011/0 ... -2010.aspx

Thanks,
Vilmondes

[Marcus Tettmar]

This doesn't work because when you open an Excel workbook via COM (which is what this is doing) the personal workbook is NOT being opened as it would be when you open manually.

See:
http://stackoverflow.com/questions/1023 ... mmand-line

The solution would be to open the personal workbook *in addition* to the workbook you are working with so that it is open and then the macro should be available.

You MAY have to do it via VBScript so that both workbooks are linked to the same Application reference - as in the stack overflow post above. Using XLOpen will probably open them separately. I haven't tested whether that will work.

[Vilmondes]

Thanks, Marcus.

Here's how I solved it (in case someone needs it):

Code: Select all

VBSTART
    Dim xlApp
    Dim xlBook
    Const xlSaveChanges = 1

    Set xlApp = CreateObject("Excel.application")
    Set objWorkbook=xlApp.Workbooks.Open("C:\Users\vilmondes\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
    Set xlBook = xlApp.Workbooks.Open("c:\temp\mycsv.csv")
    xlApp.Application.Visible = True
    xlApp.Application.run "PERSONAL.XLSB!CRLF_Removal"

    'xlBook.Save

    xlApp.ActiveWorkbook.Close xlSaveChanges

    xlApp.Quit

    Set xlBook = Nothing
    Set xlApp = Nothing
VBEND

The "objWorkbook" line is essential for the Macro to be found.

[Vilmondes]

Hi again,

I thought: "why not get this Excel Macro and run it within Macro Scheduler?", so I'm trying to convert it to be run in MS, but I'm getting the following error:
:-2147418111
Line 24, Column 12

Code:

Code: Select all

VBSTART
    Option Explicit

    Dim xlApp
    Dim xlBook
    Dim xlCalculationManual
    Const xlSaveChanges = 1

    Set xlApp = CreateObject("Excel.application")
    'Set objWorkbook = xlApp.Workbooks.Open("C:\Users\vilmondes\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
    Set xlBook = xlApp.Workbooks.Open("c:\temp\streams\toyota-export.csv")
    xlApp.Application.Visible = True
    'xlApp.Application.run "PERSONAL.XLSB!CRLF_Removal"
    
    Sub CRLF_Removal()
        Dim MyRange
        xlApp.Application.ScreenUpdating = False
        xlApp.Application.Calculation = xlCalculationManual
 
        For Each MyRange In xlApp.ActiveSheet.UsedRange
            If 0 < InStr(MyRange, vbCrLf) Then
                MyRange = Replace(MyRange, vbCrLf, "")
            End If
        Next
 
        xlApp.Application.ScreenUpdating = True
        xlApp.Application.Calculation = xlCalculationAutomatic
    End Sub
    
    CRLF_Removal()

    'xlBook.Save

    xlApp.ActiveWorkbook.Close xlSaveChanges

    xlApp.Quit

    Set xlBook = Nothing
    Set xlApp = Nothing
VBEND

Line 24 is "If 0 < InStr(MyRange, vbCrLf) Then"

The original Excel Macro is:

Code: Select all

Sub CRLF_Removal()
    Dim MyRange As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    For Each MyRange In ActiveSheet.UsedRange
        If 0 < InStr(MyRange, vbCrLf) Then
            MyRange = Replace(MyRange, vbCrLf, "")
        End If
    Next
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Any light on this? I have very few experience with VBScript.

Thanks in advance.

[hagchr]

Hi, I am not an expert on it either so not sure about the optimal code. However, I changed it to the MS format and corrected some errors. Now it will remove the string "vbCrLf" from any used cells. Hopefully it will help you forward.


VBSTART
Option Explicit
Dim xlApp
Dim xlBook
Sub Main()
Const xlSaveChanges = 1
Set xlApp = CreateObject("Excel.application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\....\xxx.csv")
xlApp.Visible = True
CRLF_Removal()
xlApp.ActiveWorkbook.Close xlSaveChanges
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
Sub CRLF_Removal()
Dim Cell
Const xlManual=-4135
Const xlAutomatic=-4105

xlApp.ScreenUpdating = False
xlApp.Calculation = xlManual

For Each Cell In xlApp.Activesheet.UsedRange.Cells
If 0 < InStr(Cell, "vbCrLf") Then
Cell.value = Replace(Cell, "vbCrLf", "")
End If
Next
xlApp.ScreenUpdating = True
xlApp.Calculation = xlAutomatic
End Sub
VBEND

VBRun>Main
  View Snippet Page

[Vilmondes]

Many thanks, hagchr, the code looks much more organized, however, I'm getting the same error at the same line =/.

Any idea?

[hagchr]

Hi, strange it works on my machine. Did you do any other changes? Maybe you can post your current version?

[Vilmondes]

Sorry, that did work. I had to remove the quotes from vbCrLF, though.

However, the code in MS takes way longer to be executed than the Excel Macro.

In Excel it takes like 1sec. in MS it takes like 15secs or so.

Do you know the reason?

Thanks again!

[hagchr]

Not sure, maybe it takes longer to go back and forth the applications and pulling the cell values?? (a question for the experts).

If you just want to clean the CSV file there are other options, for example using the MS command "CSVFileToArray" to read the whole CSV file into an array and work through the elements and then save out to a file, or (probably the best/fastest one) using the MS command "ReadFile" to read the whole file into a variable and then using "RegEx" to replace all the characters you want to change and then save the result back out to a file.

[Vilmondes]

I'm actually using both methods, hagchr. If I use MS to remove the CRLF, it will remove the ones at the end of the lines too, therefore it will break the .csv file.

I ported the Excel Macro to MS just to make sure the script will work wherever it's run i.e: in a place where we don't have the macro set up.

Just a question, please:
What do these numbers mean?

Const xlManual=-4135
Const xlAutomatic=-4105

[hagchr]

In Excel, when you are in the VBA environment and press F2 you get the Object Browser. If you look at Application / Calculation (and press F1) you can see there is a property - XlCalculation - that represents the calculation mode:

-4105 for Automatic Recalculation
-4135 for Manual
2 for Automatic except for tables.

[Vilmondes]

Humm, I got it =].

Thanks for explaining and for your help with that.

Much appreciated!