# How to pass a filename first into a VB file and then into macro?



## EGF7199 (Nov 22, 2011)

Hello,

I am new to VB and micros as my question will probably indicate.

I have a small piece of VB code that I would like use to run a macro within a .BAT file. Since I need to run this macro a bunch of times within the .BAT file, I'd like to enter the .txt filename needed by the macro from the VB command line within the .BAT file. 

So...how do I pass the .txt filename from the VB/.BAT command line into the VB code followed by passing the same .txt filename into the macro for processing?

Here's the VB command line that needs to be revised within the .BAT file to enable a .txt filename to be ingested by the VB code:

cscript //nologo ExcelMacroTest.vbs​Here's the VB code that needs to be revised in order to first accept the .txt filename and then pass it on to the macro file

Option Explicit
On Error Resume Next
ExcelMacroTest
Sub ExcelMacroTest()
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Old_Cdrive\Budget_Folder\Pareto_Histogram.xls", 0, True)
xlApp.Run "MacroText"
' xlApp.ActiveWorkbook.SaveAs "C:\TestResult.xls"
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub​And finally, here the macro I wrote that needs to be revised to accept the .txt filename passed on to it by the VB code.

Sub Pareto_Histogram()
'
' Pareto_Histogram Macro
' Macro recorded 11/23/2011 by XXX XXX
'
'
ChDir "C:\Old_Cdrive\Budget_Folder"
Workbooks.OpenText Filename:= _
"C:\Old_Cdrive\Budget_Folder\2011.txt", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
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)), TrailingMinusNumbers _
:=True
Application.Left = 44.2
Application.Top = 30.4
Range("G1").Select
ActiveCell.FormulaR1C1 = "1"
Range("G2").Select
ActiveCell.FormulaR1C1 = "2"
Range("G3").Select
ActiveCell.FormulaR1C1 = "3"
Range("G1:G3").Select
Selection.AutoFill Destination:=Range("G1:G50"), Type:=xlFillDefault
Range("G1:G39").Select
ActiveWindow.SmallScroll Down:=-33
Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A$1:$E$10000") _
, "", ActiveSheet.Range("$G$1:$G$50"), True, False, False, False
Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,1,"""")"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E51"), Type:=xlFillDefault
Range("E2:E51").Select
ActiveWindow.SmallScroll Down:=-30
Range("E2:E53").Select
Range("E53").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-41]C:R[-1]C)"
Range("E2:E53").Select
ActiveWindow.SmallScroll Down:=-30
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]<=(R[51]C[-1]/2),RC[-3],"""")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F51"), Type:=xlFillDefault
Range("F2:F50").Select
ActiveWindow.SmallScroll Down:=-30
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]<=(R53C5/2),RC[-3],"""")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F51"), Type:=xlFillDefault
Range("F2:F50").Select
ActiveWindow.SmallScroll Down:=-33
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]<=(R53C5/2),RC[-3],"""")"
Range("F2:F9").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Old_Cdrive\Budget_Folder\2011_Sorted.txt", FileFormat:=xlText, _
CreateBackup:=False
End Sub
​Any help you can give me would be greatly appreciated!


----------



## AlbertMC2 (Jul 15, 2010)

Hi 

Your Batch file (lets call it *testbat.bat*) will look something like:

```
cscript test1.vbs %1
```
When running the batch file you will create a shortcut or type the following:

```
testbat "C:\TestResult.txt"
```
Then in the VB Script file you can have something like

```
testArg = WScript.Arguments.Item(0)
[CODE]
Where testArg will then become the variable you want to use.

So when you call testbat "C:\TestResult.txt" the STRING [B]"C:\TestResult.txt"[/B] will be passed through to the [B]%1[/B] variable in the batch file which will ultimately be passed through to the WScript.Arguments.Item(0) variable in the script file and then to the variable testArg which can be used like any other variable in the script.

Note that if you have 2 or more variables:
Example 2 variables: testbat "C:\TestResult.txt" "arg2"
The batch file becomes:
[CODE]
cscript test1.vbs %1 %2
```
and the VB script file becomes:

```
testArg = WScript.Arguments.Item(0)
testArg2 = WScript.Arguments.Item(1)
...
```


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Sorry I forgot to mention when passing the variable from the VB Script to the Excel Macro you just add it to the Run Command. So:


```
xlApp.Run "MacroText", testArg
```
This should run the macro "*MacroText*" and pass the variable *testArg *to the macro.

Then the macro *MacroText *will be defined as something like:


```
Sub Macro1(test)

'
'    
MsgBox test, vbOKOnly
'
'    
End Sub
```
Note this is only an example, there are more things you can do with this and also some of the syntax changes when going from string variables to numerical variables (long/int etc)


----------



## EGF7199 (Nov 22, 2011)

Hi,

Thanks for your help!!

I think I applied your suggestions correctly, but I may have missed something because the final output file I am seeking is never generated.

*First: *
I made a .bat file entitled “*Script.bat*” that contains the 
following line:
*testbat "C:\input_filename.txt" "C:\output_filename.txt "*

·“input_filename.txt” refers to the file I would like to input into the Excel macro
·“output_filename.txt” refers to the final file generated by the Excel macro.


*Second: *
I generated a file called *Testbat.bat*. It looks like this:

*cscript ExcelMacroTest_revised.vbs %1 %2*


*Third: *
I altered the VB code to look like this (*bold text* indicates the changes I made based on your suggestions):

Option Explicit
On Error Resume Next
ExcelMacroTest
Sub ExcelMacroTest*(testArg, testArg2)*
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
*testArg = WScript.Arguments.Item(0)*
*testArg2 = WScript.Arguments.Item(1)*
Set xlBook =xlApp.Workbooks.Open("C:\Pareto_Histogram.xls", 0, True)
xlApp.Run "Pareto_Histogram", *testArg, testArg2*
' xlApp.ActiveWorkbook.SaveAs "C:\TestResult.xls"
xlApp.ActiveWorkbook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

End Sub​ 
*Fourth:*
I altered the macro code to look like this (*bold text* indicates the changes I made based on your suggestions):

Sub Pareto_Histogram*(input_filename, output_filename)*
'
' Pareto_Histogram Macro
' Macro recorded 11/23/2011 by XXX XXX


*MsgBox input_filename, output_filename, vbOKOnly'*

'
ChDir "C:\Old_Cdrive\Budget_Folder"
Workbooks.OpenText Filename:= _
"* input_filename* ", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
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)), TrailingMinusNumbers _
:=True
Application.Left = 44.2
Application.Top = 30.4
Range("G1").Select
ActiveCell.FormulaR1C1 = "1"
Range("G2").Select
ActiveCell.FormulaR1C1 = "2"
Range("G3").Select
ActiveCell.FormulaR1C1 = "3"
Range("G1:G3").Select
Selection.AutoFill Destination:=Range("G1:G50"), Type:=xlFillDefault
Range("G1:G39").Select
ActiveWindow.SmallScroll Down:=-33
Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A$1:$E$10000") _
, "", ActiveSheet.Range("$G$1:$G$50"), True, False, False, False
Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,1,"""")"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E51"), Type:=xlFillDefault
Range("E2:E51").Select
ActiveWindow.SmallScroll Down:=-30
Range("E2:E53").Select
Range("E53").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-41]C:R[-1]C)"
Range("E2:E53").Select
ActiveWindow.SmallScroll Down:=-30
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]<=(R[51]C[-1]/2),RC[-3],"""")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F51"), Type:=xlFillDefault
Range("F2:F50").Select
ActiveWindow.SmallScroll Down:=-30
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]<=(R53C5/2),RC[-3],"""")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F51"), Type:=xlFillDefault
Range("F2:F50").Select
ActiveWindow.SmallScroll Down:=-33
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]<=(R53C5/2),RC[-3],"""")"
Range("F2:F9").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"* output_filename* ", FileFormat:=xlText, _
CreateBackup:=False​End Sub

​I'm hoping that my error is a simple syntax error. Thanks again for all your help!


----------



## AlbertMC2 (Jul 15, 2010)

Hi

You are correct - it is just a syntax error here and there.

I have not tested all your Excel code just the import of the text file and then the saving of the text file.

1.
I do not know why you want to run a bat file that calls another bat file that calls the script? Why not just have the 2nd bat file and run it? But you may have a reason for doing this?
The idea of a bat file with variable arguments is so that you can run the file with different arguments at the command prompt.
Example today you may want to run *testbat "1.txt" "2.txt"*, 
but tomorrow you will run *testbat "1.txt" "22.txt"* 
The way you are doing it you always have to edit *script.bat* just to run *testbat.bat*, in which case you may as well just edit *testbat.bat* or even the *ExcelMacroTest_revised.vbs*.

2. 
The use of "*On Error Resume Next*" in your VB script allows the script to run and then just pass over any errors it may find.
If you remove this line while you are trying to write the script you will find your errors.
The first error was in your *ExcelMacroTest_revised.vbs*. You have to declare *testArg *and *testArg2*. So add the following to the other Dim statements:

```
Dim testArg, testArg2
```
You also just have to have:

```
Sub ExcelMacroTest
```
not *Sub ExcelMacroTest(testArg, testArg2)*

3.
In your Excel code you can remove the 
*MsgBox input_filename, output_filename, vbOKOnly'*
statement.
I use *msgbox *to test my variables. For example the msgbox statement here should have brought up a box giving the *input_filename* and *output_filename* with an *OK *button.
This way I would have known that the 2 filenames have passed through to your Excel code correctly.
However the msgbox code you have would not have worked. The correct code would have been something like

```
MsgBox input_filename & " " & output_filename, vbOKOnly
```
4.
Also in your Excel code:
When using variables you do not have to surround them in quotation marks unless the string value needs it. Therefore you would use:

```
Workbooks.OpenText Filename:=input_filename, Origin:=437...........etc
[B]and[/B]
ActiveWorkbook.SaveAs Filename:=output_filename, FileFormat:=xlText........etc
```

As mentioned, I have not tested the code between the *Workbooks.OpenText* and *ActiveWorkbook.SaveAs* methods
I hope you come right with these adjustments.
Let us know if you need any more help.


----------



## EGF7199 (Nov 22, 2011)

Hello again, 

I just want to start this response by thanking you again for all your help. 
It is really nice of you to do so.
Also, I am not sure of the etiquette here, so please let me know when my 
questions/requests-for-help are becoming an imposition or if I need I need to 
start paying you at some point for your help. 

That being said, here’s where I am at: 

*First *
Per your suggestion, I got rid of the extra .BAT file.

My *testbat.bat* file now looks like this

*cscript ExcelMacroTest_revised.vbs "C:\input_filename.txt" "C:\output_filename.txt "*​*Second*
I altered the VB code (*bold text* indicates the changes I made based on your suggestions):

_Option Explicit_

_*'Commented out* On Error Resume Next_

_ExcelMacroTest_

_*Sub ExcelMacroTest()*_

_Dim xlApp_
_Dim xlBook_
_*Dim testArg, testArg2*_

_Set xlApp = CreateObject("Excel.Application")_
_testArg = WScript.Arguments.Item(0)_
_testArg2 = WScript.Arguments.Item(1)_
_Set xlBook = xlApp.Workbooks.Open("C:\Just_Pareto_Histogram.xls", 0, True)_
_xlApp.Run "Just_Pareto_Histogram", testArg, testArg2_
_' xlApp.ActiveWorkbook.SaveAs "C:\TestResult.xls"_
_xlApp.ActiveWorkbook.Close_
_xlApp.Quit_

_Set xlBook = Nothing_
_Set xlApp = Nothing_

_End Sub _​*Third*
I made (I hope) a simpler version of my macro and changed
the way I entered the *input_filename, output_filename* within the code.

Sub Just_Pareto_Histogram (input_filename, output_filename)
'
' Just_Pareto_Histogram Macro
' Macro recorded 11/27/2011 by XXX XXX
'
'
Application.Left = 15.4
Application.Top = 50.2
ChDir "C:\ Old_Cdrive\Budget_Folder"
Workbooks.OpenText Filename:=*input_filename*, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
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)), TrailingMinusNumbers _
:=True
Range("G1").Select
ActiveCell.FormulaR1C1 = "1"
Range("G2").Select
ActiveCell.FormulaR1C1 = "2"
Range("G3").Select
ActiveCell.FormulaR1C1 = "3"
Range("G1:G3").Select
Selection.AutoFill Destination:=Range("G1:G50"), Type:=xlFillDefault
Range("G1:G50").Select
Application.Run "C:\Program Files\Microsoft Office\Office11\Library\Analysis\ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A$1:$E$10000") _
, "", ActiveSheet.Range("$G$1:$G$50"), True, False, False, False
Range("E2").Select
ActiveCell.FormulaR1C1 = "=+IF(RC[-1]>0,RC[-2],"""")"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E51"), Type:=xlFillDefault
Range("E2:E51").Select
ActiveWindow.SmallScroll Down:=-39
Range("E2:E51").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=*output_filename*, FileFormat:=xlText, _
CreateBackup:=False​End Sub​ 
*Results*
When I run the* testbat.bat* file I receive the following error message

Run-time error ‘1004’:
‘ATPVBAEN.XLA’ could not be found. Check the spelling of the filename, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the File menu, make sure the file has not been recently renamed, moved, or deleted.​I went and found the ‘ATPVBAEN.XLA’ on my C drive and 
added the path name (shown in blue up above) to the 
macro code but the error message still appears after I 
ran the *testbat.bat* file a second time. Any idea why 
the macro can’t find the ‘ATPVBAEN.XLA’ which it seems 
to need to generate the Pareto_Histogram?

Also the second time I run the* testbat.bat *I receive an 
additional an error message telling me that the* input_file*
is locked for editing by another user. It suggests I open 
the file as a “read only” file. Again, not sure what is 
going on here since this is my home computer and I am 
the only user. I also made sure that the command window
was closed before I ran the .bat file a second time.

And again, *THANKS*! for your help


----------



## AlbertMC2 (Jul 15, 2010)

Hi

There is no imposition at all. Carry on asking questions until you get it right.

1. 
In your VB script you do not need the brackets *()* after *Sub ExcelMacroTest* although this is not really a problem.

2.
To use the *Analysis Pack (ATPVBAEN.XLA)* you have to enable the Analysis Pack add-in. Depending on your version of Excel:
Excel 2003 : *Tools *-> *Add-Ins* -> Select *"Analysis ToolPak"* and *"Analysis ToolPak-VBA"*.
If these are not available then browse for the *ATPVBAEN.XLA* file and then select them.
Excel 2007/2010: See this document: Load the Analysis ToolPak - Excel - Office.com
Once that is done you should be able to use the Analysis ToolPak.

3. 
The reason you cannot edit the (read only) file is because when the macro gives an error it exits and the workbook is not closed automatically like you want it to do. Therefore it is still loaded in memory and is read only.
To exit the workbook you will have to got to *Start *-> *Run *-> type *taskmgr* -> Select *Excel *-> *End Task*
Once Excel is killed you will be able to open, edit and save your file again.


----------



## EGF7199 (Nov 22, 2011)

Hello Again – 

Thanks for the help- again realyy nice of you to help me. Hopefully it won’t take too much longer before my code to be up and running.

*1.*
I checked the *Tools *-> *Add-Ins* -> path and found that both the *"Analysis ToolPak"* and *"Analysis ToolPak-VBA"*.are already “checked” on the drop-down menu. I am interpreting this to mean that they are already loaded on to my computer… correct? Any other thoughts on why the macro can’t seem to contact/recognize Excel’s data analysis package? Do I need a more recent version of Excel?

*2.*
Ok, your explanation of why my Excel file is still open after I try running my macro makes sense. Easy enough to kill the file via the Window Task. Manager.


*THANKS!! *


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Try using:

```
Application.Run "'Analysis Toolpak - VBA'!Histogram", ActiveSheet.Range("$A$1:$E$10000") _
, "", ActiveSheet.Range("$G$1:$G$50"), True, False, False, False
```
Or try adding it by browsing for ATPVBAEN.XLA. If the add-in is installed correctly it should give you an error saying the file already exists.

Or try deselecting the 2 add-ins, exit Excel and then reselect them.


----------



## EGF7199 (Nov 22, 2011)

Hello – 

Thanks for your suggestions, here are my results.

*1*
I tried using the line of code you suggested 
*Application.Run "'Analysis Toolpak - VBA'!Histogram",ActiveSheet.Range("$A$1:$E$10000") _*
*, "", ActiveSheet.Range("$G$1:$G$50"), True, False, False, False*

But still receive the same ‘1004’ error​Run-time error ‘1004’:
‘ATPVBAEN.XLA’ could not be found. Check the spelling of the filename, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the File menu, make sure the file has not been recently renamed, moved, or deleted.​*2*
Not sure how to “add the "*ATPVBAEN.XLA*" by browsing” – by that do you mean to find the "*ATPVBAEN.XLA*" file online and down load it again? 

One interesting point regarding the "*ATPVBAEN.XLA*" file is that when I go to the folder where it resides C:\Program Files\Microsoft Office\Office11\Library\Analysis\ I find the following files:

*File Name Size Date Modified*
*ANALYS32.XLL* 190 KB 3/21/2007
*ATPVBAEN.XLA* 369 KB 11/27/2011 7:00PM
*FUNCRES.XLA* 82KB 6/8/2005
*PROCDB.XLA* 115KB 12/4/2000

Several Notes on the above files
1) Note how the date/time associated with the “*ATPVBAEN.XLA*” file is today. The time corresponds to when I tried to run the macro code using your # 3 suggestion. This seems strange to me – does it seems odd to you that by trying to run the _*testbat.bat* _file that I have somehow modified “*ATPVBAEN.XLA*”? I mention this because it may offer a clue as to what is going awry with my code.
2) The "*ATPVBAEN.XLA*" file is 369 KB. Does this match what you see on your computer? If different, it may be an indication of a corrupt file.


*3*
I tried the “deselecting the 2 add-ins, exiting Excel and then reselecting them”. When I try running the _*testbat.bat* _fileI receive the same "Run-time error ‘1004’ " shown in #1 above. 

*THANKS!! *


----------



## AlbertMC2 (Jul 15, 2010)

Hi

The file size of the ATPVBAEN.XLA file is correct but I do not know why the date is different. I have gone on the assumption by looking at C:\Program Files\Microsoft Office\Office11 that you are running Office 2003, is this correct?

To add the file again just go to *Tools *-> *Add-Ins* -> *Browse *-> 
Go to *C:\Program Files\Microsoft Office\Office11\Library\Analysis\* ->
Select *ATPVBAEN.XLA* -> *OK*
If the Add-in is already installed a message will display asking if you want to replace the add-in - Maybe you should replace it.

The other option you have is to repair your Office installation. If that does not work then uninstall and then reinstall Office.

Here is a macro you can use to see what the path/file of each add-in is as well as if it is enabled or not.
Start a *new *Excel workbook, then add a new macro *Macro1*
Copy and paste the following into *Macro1*

```
Sub Macro1()
    
    Dim TheXLApp As Excel.Application
    Dim CurrAddin As Excel.AddIn
    Dim seeAddin As String
    
    Set TheXLApp = Excel.Application
    
    For Each CurrAddin In TheXLApp.AddIns
        seeAddin = seeAddin & vbCrLf & CurrAddin.FullName & vbCrLf & CurrAddin.Installed & vbCrLf
    Next CurrAddin
    
    MsgBox seeAddin, vbOKOnly
    
End Sub
```
Now run the macro.
A msgbox should display with the path of each add-in. Make sure the *path of the ATPVBAEN.XLA* add-in is correct and underneath it say *True*


----------



## EGF7199 (Nov 22, 2011)

Hello,

As usual, thanks for the help! Here are my results

*1*
Yes, I am running version 2003 (3).

*2*
I replaced the “*ATPVBAEN.XLA” *file using your instructions.I did received the “This file is already here, do you want to replace it?” message. I selected “OK”. No apparent errors in this procedure. I tried running my* testbat.bat *file again and received the same ‘1004’ error shown above in past postings.

*3*
Thanks for the macro code. I installed it, ran it, and here are the results:
*C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis\ANALYS32.XLL True*
*C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis\ATPVBAEN.XLA True*
*C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\SUMIF.XLA False*
*C:\Program Files\Microsoft Office\OFFICE11\ LIBRARY \EUROTOOL.XLA False*
*C:\Documents and Settings\EGF\Desktop\Excel Query.xla False*
*C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\HTML.XLA False*
*C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\LOOKUP.XLA False*
*C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\SOLVER\SOLVER.XLA False*

*4*
I’m not sure how to “repair” my Microsoft Office installation. As far as re-installing, I’m not sure where I put the 2003 set of disks. In any case, I have been thinking of upgrading to Microsoft Office 2007 so that I will be compatible with my work computer – perhaps now is the time to do so. IYO would this solve the ‘1004’ error I have been experiencing? 

*THANKS!!!*


----------



## EGF7199 (Nov 22, 2011)

Hello – 
Me again.
I did a little test by e-mailing the 
· *t**estbat.bat* file
·*ExcelMacroTest_revised.vbs* - the file containing the VB code to run the macro (which is called within the textbat.bat file)
·*Just_Pareto_Histogram.xls* - the Excel file containing the macro
·and my test input file​to my work computer to see if it would run correctly on Excel 2007

*Results*

·Double clicking on the *testbat.bat* file generated the following security warning:
o“_The publisher could not be verified. Are you sure you want to run this software?” “File does not have a valid digital signature that verifies its publisher_”
oI selected “Run” to continue​·Within the command window I see the following comments regarding ExcelMacroTest_revised.vbs 
o_2> Microsoft VB script runtime error: Unknown runtime error_
_o‘cscript’ is not recognized as an internal or external command, operable program or batch file’_​·After the above error messages appear within the command window another window pops up with the now Infamous ” _Run-time error ‘1004’_ message stating that the ‘_Analysis Toolpak – VBA.htm’ could not be found."_ When I select the debug option provided on the error window it points to this part of the macro code: 
· _Application.Run "'Analysis Toolpak - VBA'!Histogram", ActiveSheet.Range("$A$1:$E$10000") _, "", ActiveSheet.Range("$G$1:$G$50"), True, False, False, False_​Ruling out the possibility that the “Publisher” might be causing the above errors, I’m beginning to suspect that the source of my problems might be the macro code itself. What do you think?

*THANKS!!!!*


----------



## AlbertMC2 (Jul 15, 2010)

Hi

On your work PC change the *"'Analysis Toolpak - VBA'!Histogram"* to *"'ATPVBAEN.XLA'!Histogram"* or try *"'ATPVBAEN'!Histogram"*

There is nothing wrong with your macro (on the offending line at least!). The macro now runs on my Excel 2003 with *"'ATPVBAEN.XLA'!Histogram"*

On the Excel 2007 make sure the Analysis Toolpak is enabled.

What Operating System do you have at work? Windows XP?
Check for the *cscript.exe* (application file) in *c:\windows\system32*.
Also at the *command prompt* type in *Path* and *enter*. One of the folders in your path should be *c:\windows\system32*

To repair Excel 2003 open *Excel *-> *Help *-> *Detect and Repair* -> Select "*Discard my customized settings and restore default settings*" -> *Start*.
Usually a cache of the installation files is copied to the hard drive when you first install Office. This cache will be used when repairing Office. If the cache was removed or not copied during installation then the Detect and Repair will request the Office 2003 CD. The cache is usually copied to a hidden folder on the root drive called MSOCache.


----------



## EGF7199 (Nov 22, 2011)

Wow, sorry this is taking so much of your time. I *really appreciate* you sticking with this. Here's my latest update:


*1* 
Regarding my work computer operating system is as follows:
Microsoft Windows XP
Professional
Version 2002
Service Pack 3​*2*
Regarding ensuring that the Analysis Toolpak is enabled on my work computer, I went to “_Excel Options_” window and under the “_Active Applications Additions_” list I see these two entries listed:
· Analysis ToolPak C:\Program Files\Microsoft Office\Office12\Library\Analysis\ANALYS32.XXL
· Analysis ToolPak – VBA C:\Program Files\Microsoft Office\Office12\Library\Analysis\ATPVBAEN.XLAM​So I think I am good there.


*3*
Regarding checking to see if the *cscript.exe* application file is within the *c:\windows\system32* folder of my work computer*.* I looked and the application is there- so I think we are good there too. 

*4* 
Regarding editing the macro "offending line" using your suggested *"'ATPVBAEN.XLA'!Histogram"* or *"'ATPVBAEN'!Histogram"* on my work computer
I need to verify that I am editing the macro correctly, so I am going to check with a co-worker tomorrow. For some reason when I open the Excel file that contains the macro and go to "View Macro", no macros are listed but I do see it when I open the debugger after trying to run the *testbat.bat* file. So I've been editing my macro using the debugger but at work I'm either prohibited from saving these edits generated in a "read only" file or the edits don't "stick" (i.e. the editor permits me to save the changes, but when I open up the debugger to edit it again, I see that the former edit did not take). Since a picture is worth a 1000 words, I hope I can just show my co-worker and she will be able to point out what I am doing wrong. So stay tuned for an update on this step.


*5*

Regarding repairing Excel 2003 on my home computer, the following happened. 

I went to *Excel *-> *Help *-> *Detect and Repair* -> Selected "*Discard my customized settings and restore default settings*" -> *Start*. A window pops open saying the following "_In order to correctly pick up or restore your settings, the following programs must be closed: Microsoft Excel."_ Not sure how to repair within Excel if the program is requesting that I close Excel. I also checked the Task Manager to see if I had any additional Excel processes open, but didn't see any.
*THANKS!!!!!*


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Sorry I forgot to mention that as soon as you modify the macro that you created by adding the 2 arguments after *Sub Just_Pareto_Histogram (input_filename, output_filename)* then it is removed from the macro list. So you can just go to the *visual basic editor*(alt+F11) and edit the procedure.

In Excel 2003 once you select to repair you can close Excel.The repair will continue.


----------



## EGF7199 (Nov 22, 2011)

Thanks, that last update was helpful esp. since my macro-knowing co-worker was not at work today.

I went ahead and edited the code to show *"'ATPVBAEN.XLA'!Histogram"* and then *"'ATPVBAEN'!Histogram.* Both changes resulted in the well known ‘1004’ error. 

So now I have tried running the *testbat.bat* file on both my home computer and work computer. Both times the tests ended in the ‘1004’ error, both times the process ends with the line within the macro code that "calls" the analytic tools. 

Also, when I first built the macro I "hardwired" a filename into the macro code. Later when I ran the macro within the Excel spreadsheet it worked fine i.e. it opened up the input file, performed a pareto histogram and saved data to an output file as desired. 

Not sure what all the above means (or if it means anything at all). I'm just presenting the evidence I am seeing so far.


*THANKS!!!!!!*


----------



## AlbertMC2 (Jul 15, 2010)

Hi



> when I first built the macro I "hardwired" a filename into the macro code. Later when I ran the macro within the Excel spreadsheet it worked fine


So even now when you "hardwire" the input and output files does the macro work?


----------



## EGF7199 (Nov 22, 2011)

Hello - 

Sorry, I wasn't very clear above.

By "hardwire" I mean that my macro worked ok when the input and output filenames were "hardwired" into the macro and I used the "run macro" option on the drop-down menu of my Excel spreadsheet. 

It also worked ok when I added the .vbs command line to the .BAT file instructing it to run the macro with the input and output filenames still "hardwired" into the macro code.

From what I can see the current version of the macro code has no problem using the input filename passed to it by .BAT and the .vbs file to open up the input file. It also seems to have no problem prepping the input data, as instructed by the macro code, in preparation for the pareto histogram step. The failure (as we keep seeing) happens when the macro "reaches out" to the Excel analytical software requesting it to perform the histogram – it seems like it can't "see" this code even though it is there. 

Since I work for a sizable company I can't help but feel that the software on the company’s network and on my work computer is probably ok (i.e. MicroSoft software is properly installed, all supporting files are in the correct folders, no software is corrupt, etc).

So, since I keep getting the same error message at work and at home - and since I am new to building macros - I feel that I may have inadvertently build my macro code to only "see" Excel's analytical routines if the filename is hardwired into the macro code. 

I hope that explains a little better what I was trying to say before.

*THANKS!!!!!!*


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Sorry, I understood what you said. I just thought that maybe from all the programming of the macro and vb scrips that something got messed up.
So I wanted you to hardwire the input/output files into the macro again but keep all the variable declarations etc. So in your macro only change the 2 lines like:

```
Workbooks.OpenText Filename:="c:\input.txt"........
and
ActiveWorkbook.SaveAs Filename:="c:\output.txt"......
```
But like I say leave EVERYTHING else the same. Then try running it.

Also try creating a new workbook and create a new macro that uses the histogram function.

I realize this is a lot of work just to try get a macro to work but hold on there I am sure we will get it right.


----------



## EGF7199 (Nov 22, 2011)

Hello – 

Oh, ok… gottcha now. Thanks for re-clarifying. 

I made the "hardwire" changes you suggested. The result is that I once again received the ‘1004’ error. 

I also built a new macro that generated a pareto histogram, copied the ranked values to a new Excel file and saved the Excel file as a .txt file. Of course the input and output filenames were hardwired into the macro. When I ran the macro within Excel, the code worked (i.e. the desired output file was in the correct directory. When I opened up the file the values I expected were all there). This is how the code looked.
Sub Dec_2_test()
' Dec_2_test Macro
Workbooks.OpenText Filename:= _
“*C :\directory1\directory2\input_test.txt”*, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
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)), TrailingMinusNumbers _
:=True
Range("G1").Select
ActiveCell.FormulaR1C1 = "1"
Range("G2").Select
ActiveCell.FormulaR1C1 = "2"
Range("G3").Select
ActiveCell.FormulaR1C1 = "3"
Range("G1:G3").Select
Selection.AutoFill Destination:=Range("G1:G50"), Type:=xlFillDefault
Range("G1:G50").Select
ActiveWindow.SmallScroll Down:=-84
Application.Run "ATPVBAEN.XLAM!Histogram", ActiveSheet.Range("$A$1:$E$10000" _
), "", ActiveSheet.Range("$G$1:$G$50"), True, False, False, False
Range("C2:C51").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=* “C :\directory1\directory2\output_test.txt*,” _
CreateBackup:=False
End Sub
​Next, I adapted the .vbs and macro code to permit the input and output filenames to be passed into the macro code from a command line within the . BAT file. Results: The return of the “1004” _(Method ‘Run of Object”_Application failed_) error. Once again the Debugger pointed to the macro code line containing the *ATPVBAEN.XLAM!Histogram* as the source of the problem.

In case you are interested, here how the revised .VBS file looked:
Option Explicit

'On Error Resume Next

ExcelMacroTest

Sub ExcelMacroTest
Dim xlApp
Dim xlBook
Dim testArg, testArg2
Set xlApp = CreateObject("Excel.Application")
testArg = WScript.Arguments.Item(0)
testArg2 = WScript.Arguments.Item(1)
Set xlBook = xlApp.Workbooks.Open("*C:\directlory1\ directlory2\Dec_2_test.xlsm"*, 0, True)
xlApp.Run "*Dec_2_test*", testArg, testArg2
' xlApp.ActiveWorkbook.SaveAs "C:\TestResult.xls"
xlApp.ActiveWorkbook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

End Sub​And here is how my edited macro code looks:
Sub Dec_2_test*(input_filename, output_filename)*'
' Dec_2_test Macro
Workbooks.OpenText Filename:= _
*input_filename*, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
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)), TrailingMinusNumbers _
:=True
Range("G1").Select
ActiveCell.FormulaR1C1 = "1"
Range("G2").Select
ActiveCell.FormulaR1C1 = "2"
Range("G3").Select
ActiveCell.FormulaR1C1 = "3"
Range("G1:G3").Select
Selection.AutoFill Destination:=Range("G1:G50"), Type:=xlFillDefault
Range("G1:G50").Select
ActiveWindow.SmallScroll Down:=-84
Application.Run "ATPVBAEN.XLAM!Histogram", ActiveSheet.Range("$A$1:$E$10000" _
), "", ActiveSheet.Range("$G$1:$G$50"), True, False, False, False
Range("C2:C51").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=*output_filename*, _
CreateBackup:=False
End Sub​BTW, I generated the new macro on my work computer to (hopefully) remove any possibility of the MicroSoft software being incorrectly installed or corrupted.

At this point I feel that the edits I have performed to the .BAT and VBS files and to the macro code make sense (yes?) and therefore may not be the cause of my “1004” problem. Could the way I save my edits to the macro be source of the problem? After editing the macro to enable the filenames to a pass in, I select “Save’ but receive an error message stating that is cannot save a VB project (or some kind of words to that effect). Next I go to the window it directs me to and save the file as an “_Excel Macro-Eanabled Workbook (*.xlsm_)”. Could this change be the reason why the macro code can’t ‘see” *ATPVBAEN.XLAM!Histogram*?

As usual *THANKS!!!!!!!*


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Yes, in Excel 2007 if you are using macros then you must save it as Excel Macro-Eanabled Workbook (*.xlsm). The macros won't work otherwise.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Try this:

```
Sub Dec_2_test(input_filename, output_filename) '
    ' Dec_2_test Macro
    
    Dim objXL As Excel.Application
    Dim objAdd As Excel.AddIn
    Dim i As Integer
    
    Set objXL = Excel.Application
    
    Workbooks.OpenText Filename:=input_filename, Origin:=437, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    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)), TrailingMinusNumbers:=True
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("G1:G3").Select
    Selection.AutoFill Destination:=Range("G1:G50"), Type:=xlFillDefault
    Range("G1:G50").Select
    ActiveWindow.SmallScroll Down:=-84
    For i = 1 To objXL.AddIns.Count
        If objXL.AddIns(i).Name = "ATPVBAEN.XLA" Then
            Set objAdd = objXL.AddIns(i)
        End If
    Next i
    objAdd.Installed = False
    objAdd.Installed = True
    Application.Run "Histogram", ActiveSheet.Range("$A$1:$E$10000"), "", _
    ActiveSheet.Range("$G$1:$G$50"), True, False, False, False
    Range("C2:C51").Select
    Selection.Copy
    Workbooks.Add
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:=output_filename, CreateBackup:=False
End Sub
```


----------



## EGF7199 (Nov 22, 2011)

Hello, 

*Thanks* for the code!

First the good news: The Error “1004” has gone away

Now the bad news: I have the following new error
_Run-time ‘91’:_
_Object variable or With block variable not set_​The debugger takes me to the bold line in the code (see below).
For i = 1 To objXL.AddIns.Count
If objXL.AddIns(i).Name = "ATPVBAEN.XLA" Then
Set objAdd = objXL.AddIns(i)
End If​Next i
*objAdd.Installed = False*
objAdd.Installed = True​BTW, what does the new this new piece of code do? 
If "objAdd.Installed = False" does indicate that my code can’t find *“*ATPVBAEN.XLA”? 

*Thanks again!!!!!!!!*


----------



## AlbertMC2 (Jul 15, 2010)

Hi

You did copy ALL the code, not just try to modify yours?
If you see "Object variable or With block variable not set" for that piece of code it means you are missing something like

```
Dim objXL As Excel.Application
Dim objAdd As Excel.AddIn
Set objXL = Excel.Application
```
which is at the beginning of the module.

Unfortunately it is not good news yet that you are not receiving the dreaded 1004 error. It is because you are receiving an error before the line of code that usually gives the 1004 error.

The new code just disables the ATPVBAEN.XLA addin and then enables it again. It was just something that worked for someone else in another forum - so hopefully....


----------



## EGF7199 (Nov 22, 2011)

Hello - 

Yes, I replaced all of my macro code with your code. I cut/paste a second time just to be sure but the same "_Run-time ‘91’_:" error returned. 

I Googled the error and found that the class you created may be based on a class that is present on your machine but not on my machine. Does that sound like a possibility?

Thanks!!!!


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Unfortunately the object is not specific to my PC as it is defined when the workbook is opened and Sub Dec_2_test is run.

I find it strange it would complain about that line when similar methods using the same object are run before it.
That said try replacing :

```
For i = 1 To objXL.AddIns.Count
    If objXL.AddIns(i).Name = "ATPVBAEN.XLA" Then
        Set objAdd = objXL.AddIns(i)
    End If
Next i
objAdd.Installed = False
objAdd.Installed = True
```
with :

```
For i = 1 To Excel.Application.AddIns.Count
        If Excel.Application.AddIns(i).Name = "ATPVBAEN.XLA" Then 
            Excel.Application.AddIns(i).Installed = False
            Excel.Application.AddIns(i).Installed = True
        End If
Next i
```
or with:

```
With Excel.Application
        For i = 1 To .AddIns.Count
            If .AddIns(i).Name = "ATPVBAEN.XLA" Then
                 
                .AddIns(i).Installed = False
                .AddIns(i).Installed = True
            End If
        Next i
    End With
```


----------



## EGF7199 (Nov 22, 2011)

Hello – 

Before I received your reply this morning, I have some progress (I think) on my home computer.

I tried running the new macro code you provided me earlier on my home computer (Excel 2003).

The last time I used my home computer for this project, I had just performed the “*Excel *-> *Help *-> *Detect and Repair* -> Select "*Discard my customized settings and restore default settings*" -> *Start*.” step you had recommended earlier



So next I

Reloaded the “Analysis Toolpak” and “Analysis Toolpak = VBA”
Generated a new macro called “Dec_2_test”
Edited the macro by deleting all the code I had just built and substituting your code
Edited the .VBS file to recognize the “Dec_2_test.xls” file and “Dec_2_test” macro.
Ran my *textbat.bat* file

Results

For the first time I now see an _output_test.txt_ file in my directory. However, when I open up the .txt output file it looks like this:
” "$"#,##0_);\("$"#,##0\)‑

!   "$"#,##0_);[Red]\("$"#,##0\)‑ 
" "$"#,##0.00_);\("$"#,##0.00\)‑ 
'  " "$"#,##0.00_);[Red]\("$"#,##0.00\)‑ 
7 * 2 _("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_)‑ 
. ) ) _(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)‑ 
? , : _("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)‑ 
6 + 1 _(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)à  õÿ À à  õÿ ô À à  õÿ ô À à  [1] õÿ ô À à  [1] õÿ ô À à  õÿ ô À à  ​
Also my command window still has the error message _‘cscript’ is not recognized as an internal or external command, operable program or batch file’_ alhough the cscript seems to be opening up the Excel file and accompanying macro.
So, I think we are making progress (at least on my home computer) …what do you think?

Next I'll try your suggested code edits on my work computer.

*THANKS!!!!!!!*


----------



## AlbertMC2 (Jul 15, 2010)

Hi

I noticed that as well. I believe you are trying to save your Excel file as a text file. If you open output_test.txt in Excel you will notice that it opens fine.

I am not sure if, maybe, you wanted to actually export your Excel file to a txt file *output_test.txt* instead of just saving it as a text file (although it is still in an Excel format)?


----------



## EGF7199 (Nov 22, 2011)

Hello, 

Me again.

I tried the 2 pieces of code you suggested on my work computer (Excel 2007) i.e. replace the first piece of code ("Code 1”) you listed above with the second piece of code ("Code2”), save the changes and re-run my testbat.bat file. Repeat the steps again using the third piece of code ("Code3").

Results:
In each test the Error “1004” returned however the "_Run-time ‘91’_:" error did not,

Regarding my home computer - you are correct - when I open up the _output_test.txt_ file within Excel, it looks ok. I’ll try changing my save as an “export” to see if that works.

Thanks again for all your help, I realize that I am probably only one of many that you help and I REALLY appreciate you helping me solve this problem.


----------



## EGF7199 (Nov 22, 2011)

Hello - 

Using "Help" and Goggle I tried to look up "How to export Excel data as a .txt file" and so far all I am seeing are suggestions to use the "SaveAs" command. 

Is there an "Export" command on the Excel tool bar that I am missing that I could use versus the "SaveAs" function I have been using?


*THANKS!!!!!*


----------



## AlbertMC2 (Jul 15, 2010)

Hi

You can use something like:


```
ActiveWorkbook.SaveAs Filename:=output_filename, FileFormat:=xlCSV, CreateBackup:=False
```


----------



## EGF7199 (Nov 22, 2011)

Hello - 

Sorry to be in incommunicado for awhile, I had a work project take over my life for awhile and was only able to get back to my VB/macro task tonight.

I'm not sure what happened, but the VB/maco is working on my home computer (yea!). I even tried running it within the .BAT file that I was trying to improve by automating my pareto histogram step, it worked. So looks like all systems are go.

I do have one (hopefully final) question. Each time I call the cscript in my .BAT file , it generates 2 Microsoft windows. One asks me if I want to save the changes to my new output file (I do) and another one asks if I want to save the changes to my input file (I don't). Is there an automatic way to answer these questions?

As always, thanks again for all your help. While I'm not sure why the code is working, I do know that it had to do with suggestions.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

I am glad it seems to be working. That is just like computers - one minute it is not working, play around a little yet ultimately do nothing and then it starts working. 

You can try putting:

```
Application.DisplayAlerts = False
```
before this line at the end:

```
ActiveWorkbook.SaveAs Filename:=output_filename, FileFormat:=xlCSV, CreateBackup:=False
```


----------



## EGF7199 (Nov 22, 2011)

Hello - 

Sorry to be out of contact - once again a work project required ASAP attention.

I tried the your above code suggestion and it worked like a charm (yea!). So as near as I can figure, my code is good to go.

THANK YOU!!!!!!!!!!!!!!!!!!!!!! once again for all your help. I believe all your help falls into the "above and beyond" category and it is/was VERY MUCH appreciated!!!!!!!!!


----------



## AlbertMC2 (Jul 15, 2010)

It is a pleasure. I am glad you got it working. I must admit I learnt a few things along the way as well so hopefully we both benefited.


----------

