I want to create a macro in VBA to make excel file in specific location, but the filename have to be provided from userform.
So far here’s my code:
Dim wbname, wbsheet, wblocat As String
Dim wbghkv As Double
wbname = devserv.wbnamebox 'name of workbbok from userform
wbsheet = devserv.wbsheetbox 'name of worksheet from userform
wbghkv = devserv.ghkvbox 'value of gerate hkv from userform
wblocat = "T:\nf\usefullstuff\excel" 'location of file folder
Workbooks.Add.SaveAs Filename:="wblocat \ wbname"
Workbooks(wbname).Activate
Worksheets.Add
Worksheets.Add.Name = wbsheet
But I get an error at Workbooks.Add.SaveAs…..
Before I tried to use chdir function before the Workbooks.Add.SaveAs but the file was saved in mydocuments .
Could you please help me ?
>Solution :
You’re trying to save as the literal string "wblocat \ wbname".
Instead, try Workbooks.Add.SaveAs Filename:=wblocat & "\" & wbname.
As an aside, Dim wbname, wbsheet, wblocat As String declares wbname and wbsheet as Variant. If you want them to be String, you’ll need to use Dim wbname As String, wbsheet As String, wblocat As String.