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 ?
CodePudding user response:
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
.