Programs1234
08-09-2017, 11:41 AM
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?
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?