Arguments in Sub

[aner1755]

Hi All!
I wish to transform a sub to a more useful one since I'll use it sevearal times in different contexts. The sub showed below opens an Excel-workbook and clears worksheet nr 1 from its data.
What I have in mind is to declare two arguments for the sub, one argument should be the workbook and the other one the worksheet.

There would be nice to call the sub like;
Call ExpAnalys_Click(NewWorkbook.xlsm, 2)
Could someone please help me out with this?

Cheers!


Private Sub ExpAndAnalys_Click()
Dim ObjXLApp As Object
Dim ObjXLBook As Object
Dim oSheet As Object
Set ObjXLApp = CreateObject("Excel.Application")
Set ObjXLBook = ObjXLApp.Workbooks.Open("C:\Documents and Settings\anderse\Skrivbord\Jobb\Nyskapat (till H)\Sharp\ExpAndAnalys.xlsm")
Set oSheet = ObjXLBook.Worksheets(1)
ObjXLApp.Visible = True

'Clear the excel sheet from previous data
oSheet.Activate
oSheet.UsedRange.Select
ObjXLApp.Selection.ClearContents
End Sub

 

[Banana]

Change your first line of Sub from this:

Private Sub ExpAndAnalys_Click()

to this:

Private Sub ExpAndAnalys_Click(sWorkBook As String, sWorkSheet As String)

Then you would replace in the variable where appropriate:

Set ObjXLBook = ObjXLApp.Workbooks.Open(sWorkBook)
Set oSheet = ObjXLBook.Worksheets(sWorksheet)

And as a note, you may want to think about taking out Excel variable and make it more persistent so you are not opening and closing Excel everytime you call that sub.

 

[aner1755]

Thanks Banana!

It was fairly easy.

Have a blessed day!