423,099 Members | 2,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,099 IT Pros & Developers. It's quick & easy.

Pass a parameter to an excel function using vbscript

P: 18
Hi,

I'm trying to pass a parameter to an VBA Excel function but I always got this error:

Type Mismatch error

This is my sample Excel function:


Expand|Select|Wrap|Line Numbers
  1. Public Function OpenAndRepairWorkbook(X As String) As String
  2. Dim oWB As Workbook
  3. On Error GoTo Err_Open
  4. Application.DisplayAlerts = False
  5. Set oWB = Workbooks.Open(Filename:="D:\UnitTest.xlsm", CorruptLoad:=XlCorruptLoad.xlRepairFile)
  6. oWB.SaveAs ("D:\UnitTest.xlsm")
  7. OpenAndRepairWorkbook = "VB-00"
  8. oWB.Close
  9. Exit Function
  10. Err_Open:
  11. OpenAndRepairWorkbook = "VB-98"
  12. Err.Clear
  13. End Function
And this is my sample vbscript code:

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim ArgObj, var1, var2, var3
  3. Dim X
  4. X = "Sample"
  5. Set ArgObj = WScript.Arguments 
  6. var1 = ArgObj(0) 
  7. var2 = ArgObj(1) 
  8. var3 = ArgObj(2)
  9. Set objExcel1 = CreateObject("Excel.Application")
  10.     objExcel1.DisplayAlerts = 0
  11. Set objWorkbook1 = objExcel1.Workbooks.Open(var1)
  12.     result = objExcel1.Run("UnitTest.xlsm!OpenAndRepairWorkbook"(X))
  13.     if err.Number<>0 then
  14.     WScript.Echo "VB-98," + err.Description
  15.     else
  16.     WScript.Echo result
  17.     end if
  18.     objWorkbook1.Save
  19.     objExcel1.Quit
  20. Set    objExcel1 = Nothing
  21. set ArgObj = Nothing
  22.  
Given these codes, I'm confused why I got "type mismatch" error even though the required parameter is String.

Is it possible to pass a parameter from vbscript upto the VBA function?

Thanks.
Aug 23 '11 #1
Share this Question
Share on Google+
1 Reply


P: 18
Problem solved!

For future reference.

My VB script code:

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim ArgObj, var1, var2, var3 
  3. Set ArgObj = WScript.Arguments 
  4.  
  5. 'First parameter target file
  6. var1 = ArgObj(0) 
  7. 'Second parameter validation file
  8. var2 = ArgObj(1) 
  9. 'Third parameter macro
  10. var3 = ArgObj(2)
  11.  
  12. Set objExcel1 = CreateObject("Excel.Application")
  13.     objExcel1.DisplayAlerts = 1
  14. Set objWorkbook1 = objExcel1.Workbooks.Open (var1)
  15. result = objExcel1.Run (var2, var3)
  16.     if err.Number<>0 then
  17.     WScript.Echo "VB-98," + err.Description
  18.     else
  19.  
  20.     WScript.Echo result
  21.     end if
  22.     objExcel1.Quit
  23. Set    objExcel1 = Nothing
  24. Set ArgObj = Nothing

and my VBA function:


Expand|Select|Wrap|Line Numbers
  1. Public Function OpenAndRepairWorkbook(filePath) As String
  2.  
  3. Dim oWB As Workbook
  4.  
  5. On Error GoTo Err_Open
  6.  
  7. Application.DisplayAlerts = False
  8.  
  9. Set oWB = Workbooks.Open(Filename:=filePath, CorruptLoad:=XlCorruptLoad.xlRepairFile)
  10. oWB.SaveAs (filePath)
  11.  
  12. oWB.Close
  13.  
  14. OpenAndRepairWorkbook = "VB-00"
  15.  
  16. Exit Function
  17.  
  18.  
  19. Err_Open:
  20.  
  21. OpenAndRepairWorkbook = "VB-99" + "," + Err.Description
  22.  
  23. Err.Clear
  24.  
  25.  
  26. End Function
  27.  
Aug 24 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.