Excel VBA - mkdir with variable names

Question

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

Bond · Accepted Answer · 2015-08-10 18:03:04Z

2

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

edited Aug 10, 2015 at 18:03

Bond

16.3k6 gold badges34 silver badges56 bronze badges

answered Aug 10, 2015 at 16:30

dev1998

8928 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rodlonghorn Over a year ago

That's it! Thank you.

Collectives™ on Stack Overflow

Excel VBA - mkdir with variable names

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related