CODEWITHSUNDEEP
excelvbasyntaxmkdir
rodlonghorn
rodlonghorn

Reputation: 15

Excel VBA - mkdir with variable names

I have a macro that copies data into a workbook and saves the workbook as a combination of variables in the input workbook. Here is what I'm using to do that:

ActiveWorkbook.saveas "D:\sm031648\Documents\Lighting Audits\" & Accnt & " -     " & Property & ".xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False

I would like to add an if statement and mkdir before so that it saves it within a folder of the same name, and creates the folder in the directory if one is not already there. As I type in the below code above this part with variable names, I get "Compile Error; Expected end of statement". What am I missing?

If Len(Dir("D:\sm031648\Documents\Lighting Audits\" & Accnt & " - " Property, vbDirectory)) = 0 Then
MkDir "D:\sm031648\Documents\Lighting Audits\" & Accnt & " - " Property
End If

Upvotes: 1

Views: 4485

Answers (1)

dev1998
dev1998

Reputation: 892

You are missing the & before Property.

If Len(Dir("C:\sm031648\Documents\Lighting Audits\" & Accnt & " - " & Property, vbDirectory)) = 0 Then
    MkDir "C:\sm031648\Documents\Lighting Audits\" & Accnt & " - " & Property
End If

Upvotes: 2

Related Questions