Passing a string value from C# to VBA Macro

Hello everyone

I have a VBA Macro (xlsm Format) which contains 2 functions (one for Copying  the cells data of one sheet to another sheet  and the other function should save the sheet that has been copied ).
So I'm running the VBA macro from C# (Microsoft visual Studio 2010) against a xlsx report and I'm trying to pass a string  from C# to the both functions Copy and Save which are within the VBA Macro like this:
//The function which should  run the macro in C#
 public void RunMacro(object oApp, object[] oRunArgs)
        {
            oApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod,
             null, oApp, oRunArgs);       
        }
// The part of the code where the macro is runned in C#
        #region Run Makro automatically
                try
                {
                  string  temporaryPath = "D:\TEMPs\" ,                 
                   reportName = "Testreport.xlsx";
                   string macroName = "Testmakro.xlsm";

                    // Object for missing (or optional) arguments.
                    object oMissing = System.Reflection.Missing.Value;

                    // Create an instance of Microsoft Excel
                    Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
                     
                    // Make it visible
                    oExcel.Visible = true;
                    oExcel.ScreenUpdating = false;
                    bool reportExist = Directory.EnumerateFiles(temporaryPath + "\\", reportName).Any();

                    if (reportExist == true)
                    {

                        // Open Worksheet01.xlsm
                        Excel.Workbooks oBooks = oExcel.Workbooks;
                        Excel._Workbook oBook = null;

                        oExcel.DisplayAlerts = false;

                        oBook = oBooks.Open( temporaryPath + "\\" + macroName, oMissing, oMissing,
                            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
              
                        //Run the Macro 
                        //RunMacro(oExcel, new Object[] { macroName, temporaryPath });
                    }
                    else
                    {
                        string Failure = "XXXXXX ";
                        Err01.ErrorLog(pathOutput, Failure);
                    }
                }
                catch
                {
                    string Failure1 = "Failure by running the macro! \n ";
                    GlobalVar.Err01.ErrorLog(pfadOutput, Failure1);
                }
// the Function Copy() and Save() in the VBA macro 
Sub Copy(temporaryPath As String)
Dim strFile As String
Dim fileNAME As String
fileNAME = "Testreport.xlsx"
'Do some stuff
'strFile = Environ("MAKROPATH") & fileNAME 
strFile = temporaryPath & fileNAME 
//Do some stuff
End Sub

Sub Save (temporaryPath As String)
 Dim FPath As String
Dim FName As String
FPath = temporaryPath
FName = "Success.xlsx"
//Do some stuff
 Application.ActiveWorkbook.SaveAs Filename:=FPath & FName, FileFormat:=51, AccessMode:=xlNoChange
//Do some stuff
End Sub

Open in new window

The issues I'm facing is that the parameter temporaryPath that I'm passing to the run function in C# won't be transferred to the functions Copy and Save within the macro!
The approach with Environ("MACROPATH") works just great but I have been told not to use  environment variables.
How Can I pass the string value to the run function in C# so that it will be used in the Copy and save functions within the VBA macro?

Thank you
Cony TNAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
You could also have a hidden worksheet and save these variables to this sheet for consumption by the C# app
Cony TNAuthor Commented:
Thank you for answering.Till now I've been only programming in C# .VBA and MS Excel  (macro) are for me  kinds of "new Land". I'm afraid I didn' t understand your suggestion. The question may sound silly but how does one create a hidden  worksheet? I still don't understand neither how these variables in the hidden worksheet coulb be accessible from C# ...
Michael FowlerSolutions ConsultantCommented:
Create a new sheet as per normal
In the Microsoft Visual Basic Editor select the sheet
In the worksheet properties window change the Visible field "2 xlSheetVeryHidden"

This makes the worksheet completely hidden to any users of the workbook but it is still available via code. So what you do is save the variables to specific cells on this sheet and then from C# you can reference these cells to get these variables out for your use. The idea behind the hidden sheet is that that this locks the sheet and prevents users from trying to store their own data in it
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

MlandaTCommented:
Here is what i use for a very similar application. User selects options on UI and I pass them to my Excel file (it's VB.NET, but that's easy to convert to C#.
Dim xlApp As New excel.Application
Dim xlWorkBook As excel.Workbook = Nothing

Try

	'Start Excel and open the workbook.
	xlWorkBook = xlApp.Workbooks.Open(ExcelReportFilePath, IgnoreReadOnlyRecommended:=True, Editable:=True, UpdateLinks:=True, Notify:=False)

	'Send in the new parameters from the UI
	xlApp.Run("UpdateConnectionFile", Periods, Company)

	'Clean-up: Close the workbook and quit Excel.
	xlWorkBook.Close(SaveChanges:=True)

Catch ex As Exception
	Log.Error(ex)
Finally

	'Quit Excel
	xlApp.Quit()

	'Clean Up objects
	System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
	System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)

End Try

Open in new window

In the Excel file, I have a macro called UpdateConnectionFile and the line xlApp.Run("UpdateConnectionFile", Periods, Company)takes care of passing in the parameters. the macro is in a module, and it looks like this:
Sub UpdateConnectionFile(ByVal Periods As String, ByVal Companies As String)

    Dim ary As String
    ary = BuildFilterQuery(Periods, Companies)

    'do other stuff here...

End Sub

Open in new window

Cony TNAuthor Commented:
Thank you both for your suggestions... I'm going to try the two suggestions. I have a question please...

@MlandaT: Since my macro (Testmakro.xlsm) contains a module with the following :
Sub Copy(ByVal temporaryPath As String)
....
End sub
and
 Sub Save(ByVal temporaryPath As String)
....
End sub

Open in new window

How should I call the Run Method in C# ?
string  temporaryPath = "D:\TEMPs\" ,                 
reportName = "Testreport.xlsx";
string macroName = "Testmakro.xlsm";

RunMacro(oExcel, new Object[] { "Copy", "Save",  temporaryPath })
//Or is it 
RunMacro(oExcel, new Object[] {macroName, "Copy", "Save",  temporaryPath })

Open in new window


Thank you
MlandaTCommented:
As indicated in the example (you need to look at my macro signature as well):
xlApp.Run("UpdateConnectionFile", Periods, Company)

You have TWO macros. One call per macro. You cannot combine them into one call. If you want to combine them, then you need to combine the VBA subs into one method. In this case, with the two macros, you need to issue to calls like so:
oExcel.Run("Copy", temporaryPath)
oExcel.Run("Save", temporaryPath)

Note that the syntax in oExcel.Run("NameOfMacroSub", Parameter1, Parameter2, Parameter3....)

NOTE: The Application.Run method (https://msdn.microsoft.com/en-us/library/office/ff197132.aspx) takes the name of the macro, and the parameters that should be passed to the macro. If the macro has a single parameter, you can only pass one parameter. if youe macro has 2 parameters, you can also only pass in 2 parameters (not 1, not 3, not 4, just 2). Your macros each have one parameter.
Cony TNAuthor Commented:
Thank you for the hints...
@MlandaT: I'm having following error:
System.Runtime.InteropServices.COMException: Exception  of HRESULT: 0x800A03EC

I thought the error was raised up because I did'nt close the workbook and quit Excel but it wasn't.
I searched the internet but I still don't understand the error...
Do you have any idea?

Thank you
MlandaTCommented:
On which line in our code snippet does that exception occur - if you examine the stacktrace on that exception, you will get a line number and filename?

That said, a search on that error suggests that this happens on the Save? What is the version of Excel that you are automating? Is this running on a web server in IIS or just a normal desktop application?

Some people are suggesting that it might have to do with Office 2003 and access to the desktop... http://stackoverflow.com/questions/4408336/system-runtime-interopservices-comexception-0x800a03ec (take note.. someone says: I've just used SaveCopyas() instead of SaveAs() and added impersonate in web.config and its working!)
Cony TNAuthor Commented:
I'm using Excel 2010 and it's runninbg on a normal desktop application.
I've adjusted impersonate in web.config and instead of SaveAs() I'm using SaveCopyAs() but the whole process just breaks by entering the function Save(ByVal temporaryPath As String) and the error message in VBA says: named argument not found and the yelow arrow is pointing at  Save(ByVal temporaryPath As String) ...
Error2.PNG
MlandaTCommented:
And if we rename that sub from "Save" to someone different from anything that Excel uses?

What is happening inside that sub?
Does it have to be a macro? Or should we rather just call save on the Excel object?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cony TNAuthor Commented:
I renamed that "Save" to SaveAfterFormating(ByVal temporaryPath As String) and it looks like this:

Sub SaveAfterFormating(ByVal temporaryPath As String)
Dim FName As String
Dim FPath As String

FName = "DataFormated.xlsx"
FPath = temporaryPath

Application.ActiveWorkbook.SaveAs Filename:=FPath & FName, FileFormat:=51
ActiveWorkbook.Close SaveChanges:=True

'Close Excel 
   With Application
    .DisplayAlerts = False
    .Quit
   End With

End Sub

Open in new window

Although I renamed it I still have the same error message...
Then I tried something else (Try calling the save Method on the Excel object) and comment the save macro and try to save from C# this way:
 // Object for missing (or optional) arguments.
    object oMissing = System.Reflection.Missing.Value;

 // Create an instance of Microsoft Excel
   Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
   Excel.Workbooks oBooks = oExcel.Workbooks;
    Excel._Workbook oBook = null;
 
 oBook = oBooks.Open(temporaryPath + "\\" + makroName, oMissing, oMissing,
                      oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                      oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

 oExcel.Run("Copy", temporaryPath);

 oBooks.Application.ActiveWorkbook.SaveAs(temporaryPath + "\\DataFormated.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing,oMissing,
                       oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing, oMissing, oMissing);

 oBook.Close(false, oMissing, oMissing);

  oExcel.Quit();

 System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
  System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);

Open in new window

Saving in C# works just fine. No more error message!!! After the saving process the  saved file DataFormated.xlsx shoud be moved to another folder X. After it has been moved to the folder X it happens that the file DataFormated.xlsx doesn't contain ANY DATA at all!! BUT as it was in the tempory path with the macro  it contained data... Am I missing something in my code???
Thank you
MlandaTCommented:
Out of curiosity... did you eventually manage to get it right? How did you work around that last hurdle? (I got a bit tied up and couldn't respond to your last questions)
Cony TNAuthor Commented:
As you suggested I called the SaveAs Method on the Excel object in C# and didn't use the save macro anymore.
I don't know what went wrong the first time as I executed the code but the second time
I did it again all the data were in the DataFormated.xlsx after
it has been moved to the folder X ...
Thank you for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.