How do I set passwords and how to save as a certain name for a file

[Programs1234]

I am kind of new to excel VBA and am currently having some difficulties automating a daily report that I have to run everyday.

Two things I need help with: First being that I need to know if anyone here knows of a way to program VBA to Password protect a workbook.



Second being that I am trying to "Save As" a file in a particular name. This is what I have so far:



filename = Workbooks("Macros").Worksheets("Vanguard Nightly Recalls").Cells(1, 2)
xdate = workbooks("Macros").Worksheets("Vanguard Nightly Recalls").cells(2, 2)
time = workbooks("Macros").Worksheets("Vanguard Nightly Recalls").cells(3, 2)

Workbooks(filename).Activate

Workbooks(filename).Worksheets("Domestic Recalls").Select

ActiveWorkbook.SaveAs filename:= _
"S:\Stock Loan\Clients\Vanguard\Reporting\Nightly Recalls Files\Vanguard All Recalls.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


towards the end of the file before the ".xlsx" part, I am trying to add the variables "xdate" and "time", which are both from a different workbook into the file name. So for example, today is 8.09.17 at around 2:30PM. I want to be able to fill in those two pieces of info into the "Macros" workbook and be able to save a different file with those two pieces of info. An example of what the file name should be is: "Vanguard All Recalls 2PM 08.09.17" Does anyone here know how to properly attach these variables?

[mdmackillop]

Something like this. Note, you can't use Time as a variable
Check the immediate window for the debug result

With Workbooks("Macros").Worksheets("Vanguard Nightly Recalls")
Filename = Left(.Cells(1, 2), Len(.Cells(1, 2)) - 5)   'Assumes .xlsx or similar suffix; change if required
xdate = .Cells(2, 2)
Tim = .Cells(3, 2)
End With


pth = "S:\Stock Loan\Clients\Vanguard\Reporting\Nightly Recalls Files\"


fname = pth & Filename & " " & Tim & " " & xdate & ".xlsx"
Debug.Print fname
Workbooks(Filename).SaveAs Filename:=fname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

[Programs1234]

Thanks a lot for the help! One issue: I tried running the program and I keep getting this statement "Method 'SaveAs' of object_' Workbook failed. So, I am assuming that everything is all set except for that last line. What do you think the problem is?

[mdmackillop]

Look in the immediate window at the created file name (VBE View/Immediate Window) and check how it looks; no illegal characters etc. How is Date formatted?

[Programs1234]

Hi. I actually fixed up some stuff(illegal characters, formatting, and etc) and the code finally works now. Thanks a lot! I really liked how you turned the path into a variable. Never really thought about that, but I will be sure to keep this in mind for my next automation projects.

[Programs1234]

On an unrelated note, is there a way to password protect with VBA? I tried recording a macro as I placed a password on a file and it did not seem to have recorded any codes.

[mdmackillop]

What do you want to protect?

[Programs1234]

I just found out how to do it. I wanted to protect the workbook that I was running the other functions for. I realized that all I had to do was add Password:= " " right after the ".SaveAs" function. Thanks!

[mdmackillop]

" " is not a very strong password

[Programs1234]

Yeah, I know what you mean. I only put that down as an example. I appreciate the help. Thanks!