Set workbook name not working

J

JayDe

Hi

I am trying to set the workbookname in a sub procedure but it is not working.
Her are the part from my code that is not working:

Private Sub Start_Click()

Dim MalWb as workbook
Dim MalTxt as String

Set MalWb = Workbooks(ActiveWorkbook.Name)
MalTxt = ActiveWorkbook.Name

' Here I have som code to put data into MalWb

Call ReskontroArk("Kunder", MalTxt)


End Sub


Private Sub ReskontroArk(ReskontroType, MalTxt As Variant)

Dim MalWb As Workbook
Set MalWb = Workbooks(MalTxt) ' run time error 1004. Canot find file

End Sub

Can someone see what I have done wrong
 
J

Jacob Skaria

Hi

I am unable to recreate an error..The code looks fine.

Few other points
--Set MalWb = Workbooks(ActiveWorkbook.Name)
is same as
Set MalWb = ActiveWorkbook

--To re-write your code the below would do. The procedure ReskontroArk takes
the second argument as a workbook object instead of string variable

Private Sub Start_Click()
Call ReskontroArk("Kunder", ActiveWorkbook)
End Sub


Private Sub ReskontroArk(ReskontroType, MalWb As Workbook)
MsgBox MalWb.Name
End Sub
 
D

Dave Peterson

I don't see the error. I'd add a "msgbox maltxt" in a few spots to make sure
the string variable was what I expected it to be.

But I'd do something like:

Private Sub Start_Click()

Dim MalWb as workbook

Set MalWb = ActiveWorkbook

Call ReskontroArk("Kunder", MalTxt)

End Sub


Private Sub ReskontroArk(ReskontroType as String, MalWb As Workbook)

'and just use that passed variable...
msgbox malwb.fullname

End Sub
 
J

JayDe

Thank you Jacob, this worked.

I am howewer using several WorkBooks in my macro, and I try to avoid using
ActiveWorkbook, and instead assign a variable name to the WorkBook I am
working on. Could I used a variable in the call procedure, instead of the
ActiveWorkbook

Regards
JayDe
Norway
 
J

Jacob Skaria

You can..

Private Sub Start_Click()
Call ReskontroArk("Kunder", ActiveWorkbook.Name)
End Sub

Private Sub ReskontroArk(ReskontroType, strWb As String)
MsgBox Workbooks(strWb).Name
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top