Home > OS >  VBA file operations
VBA file operations

Time:03-15

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.

  • Related