Cony TN
asked on
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 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
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
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
You could also have a hidden worksheet and save these variables to this sheet for consumption by the C# app
ASKER
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# ...
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
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
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#.
nFile", Periods, Company)takes care of passing in the parameters. the macro is in a module, and it looks like this:
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
In the Excel file, I have a macro called UpdateConnectionFile and the line xlApp.Run("UpdateConnectioSub UpdateConnectionFile(ByVal Periods As String, ByVal Companies As String)
Dim ary As String
ary = BuildFilterQuery(Periods, Companies)
'do other stuff here...
End Sub
ASKER
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 :
Thank you
@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
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 })
Thank you
As indicated in the example (you need to look at my macro signature as well):
xlApp.Run("UpdateConnectio nFile", 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.
xlApp.Run("UpdateConnectio
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
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.
ASKER
Thank you for the hints...
@MlandaT: I'm having following error:
System.Runtime.InteropServ ices.COMEx ception: 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
@MlandaT: I'm having following error:
System.Runtime.InteropServ
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
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!)
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!)
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I renamed that "Save" to SaveAfterFormating(ByVal temporaryPath As String) and it looks like this:
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:
Thank you
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
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);
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
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)
ASKER
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
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