-
May 21st, 2019, 02:30 AM
#1
Thread Starter
Addicted Member
[RESOLVED] Save Workbook
Hi,
I wish to save down a particular workbook to a particular path with an exact name and yesterday's date.
91 Daily Revenue 13 05 2019_KM
I wish to save the file as the above, but with the current variables constantly updating.
91 represents the current workday in the year and the date save down as the previous days date.
My current code only saves a blank workbook in the below path.
I searched all websites but couldn't find anything that matches my exact requirements.
Sub SaveWorkbookSet()
Dim wkb As Workbook
Set wkb = Workbooks.Add
wkb.SaveAs "N:\My Documents\Daily Revenue.xls"
Worksheets("Control").Cells(31, 5) = Now
-
May 21st, 2019, 04:12 AM
#2
Re: Save Workbook
The date for yesterday is not that hard.
That's:
Code:
Dim dtYesterday As Date, sYesterday As String
dtYesterday = Date - 1
sYesterday = Format(dtYesterday, "DD MM YYYY")
The number of workdays that's something different.
What do you describe as the number of workdays?
-
May 21st, 2019, 06:00 AM
#3
Re: Save Workbook
he number of workdays depends on how many weekdays and holidays are counted
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 21st, 2019, 06:39 AM
#4
Thread Starter
Addicted Member
Re: Save Workbook
Workday represents the business day in the year.
for example.
Friday = 94
Saturday = 0
Sunday = 0
Monday = 95
Tuesday = 96
etc etc
I also want to save down the workbook to a particular path.
-
May 21st, 2019, 07:10 AM
#5
Re: Save Workbook
as your holidays are different to mine and many others hard to answer
from your other thread if the workdays are already in a cell just read from that
Code:
mypath = "N:\My Documents\ "
myfilename = mypath & range ("i8") & " Daily Revenue " & format(date, "dd mm yyyy") & "_KM.xls"
debug.print myfilename
wkb.SaveAs myfilename
this is untested, so give it a try
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 21st, 2019, 07:23 AM
#6
Thread Starter
Addicted Member
Re: Save Workbook
I get a run time error 424 on this? :-(
-
May 21st, 2019, 07:37 AM
#7
Re: Save Workbook
I get a run time error 424 on this
presumably because wkb is not assigned, it needs to be part of your original code
you may also need to specify the worksheet containing the cell with workday
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 21st, 2019, 08:21 AM
#8
Thread Starter
Addicted Member
Re: Save Workbook
Nearly worked :-)
Workday is missing before the word - Daily
Also. Why does it only save down a blank workbook?
I want to save down the whole work book I'm working from
-
May 21st, 2019, 08:23 AM
#9
Thread Starter
Addicted Member
Re: Save Workbook
(forgot to add this)
Need the file name to look like this
80 Daily Revenue 25 04 2019_KM
-
May 21st, 2019, 04:29 PM
#10
Re: Save Workbook
Also. Why does it only save down a blank workbook?
try changing to
Code:
mypath = "N:\My Documents\ "
wrkday = 80 ' you need to get this value from some source, see below
myfilename = mypath & wrkday & " Daily Revenue " & format(date, "dd mm yyyy") & "_KM.xls"
debug.print myfilename
thisworkbook.SaveAs myfilename
as you already have a formula in some worksheet to calculate the work day number you could get the value from there, or duplicate the look up table and formula in this workbook then get the value from there, else you will need a function which considers an array all the holidays to return the number of working days
there is a worksheetfunction that can help to do this
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 22nd, 2019, 04:43 AM
#11
Thread Starter
Addicted Member
Re: Save Workbook
Hi,
The workday is currently worked out via a formula in cell I8.
How do I include this cell in the above code?
-
May 22nd, 2019, 05:28 AM
#12
Re: Save Workbook
i made a function to return the number of workdays
Code:
Function workdays() As Integer
hols = Array(CDbl(CDate("01/01/2019")), CDbl(CDate("19/04/2019")), CDbl(CDate("22/04/2019")), CDbl(CDate("06/05/2019")), CDbl(CDate("27/05/2019")), CDbl(CDate("26/08/2019")), CDbl(CDate("25/12/2019")), CDbl(CDate("26/12/2019")))
workdays = WorksheetFunction.NetworkDays(CDate("01/01/2019"), Date, hols)
End Function
use like
Code:
mypath = "N:\My Documents\ "
myfilename = mypath & workdays & " Daily Revenue " & format(date, "dd mm yyyy") & "_KM.xls"
debug.print myfilename
thisworkbook.SaveAs myfilename
as it is, the values in the array have to be updated every year, it should be possible to calculate the values for all years, easter being the most difficult and whether some other day is allowed as holiday when some proclaimed holiday falls on a weekend
The workday is currently worked out via a formula in cell I8.
How do I include this cell in the above code?
you would put a reference to the range i8, as in post #5
BUT if i8 is not on the active sheet you would need to qualify the range with the worksheet, like sheets("mysheet").range("i8")
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 22nd, 2019, 07:57 AM
#13
Thread Starter
Addicted Member
Re: Save Workbook
OK that all worked. However it just renames the file I'm working on. How do I save down an additional file with the new filename as described in #10
(Basically just want to copy the workbook I'm working on and save in a new folder with the above filename)
-
May 22nd, 2019, 04:08 PM
#14
Re: Save Workbook
thisworkbook is always the workbook containing the code that is running
i do not know if you already have a workbook object for the workbook you are working on, try changing to activeworkbook
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 23rd, 2019, 05:36 AM
#15
Thread Starter
Addicted Member
Re: Save Workbook
I tried the code below....
and it came back with an error message like this...
---------------------------
Microsoft Visual Basic for Applications
---------------------------
400
Never seen this error message before.
Sub Saveworkbookset()
Dim wkb As Workbook
Dim Wks As Worksheet
Set wkb = Workbooks.Add
myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
Debug.Print myfilename
ActiveWorkbook.SaveAs myfilename
Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
-
May 23rd, 2019, 05:52 AM
#16
Re: Save Workbook
and it came back with an error message like this...
which line?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 23rd, 2019, 07:59 AM
#17
Re: Save Workbook
Code:
Sub Saveworkbookset()
Dim wkb As Workbook
Dim Wks As Worksheet
Set wkb = Workbooks.Add
myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
Debug.Print myfilename
ActiveWorkbook.SaveAs myfilename
Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
Again you are mixing implicit and explicit references.
You start with:
Code:
Set wkb = Workbooks.Add
wkb is then never used
Next
Code:
ActiveWorkbook.SaveAs myfilename
Another implicit reference.
Should it be wkb.SaveAs myFilename ??
Then out of nowhere
Code:
Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
Where Wks defined and assigned a value?
-
May 23rd, 2019, 10:21 AM
#18
Thread Starter
Addicted Member
Re: Save Workbook
Sorry I'm bit of beginner at this and learning as I go along.
Then out of nowhere
Code:
Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
Where Wks defined and assigned a value?
What should it be instead?
The reason I added active workbook because my original code was saving down a blank workbork. I wanted to create a copy of the work book I was working from and save it down to a particular path.
Are you saying this part of code will work in stead?
Next
Code:
ActiveWorkbook.SaveAs myfilename
Another implicit reference.
Should it be wkb.SaveAs myFilename ??
-
May 23rd, 2019, 04:05 PM
#19
Re: Save Workbook
Should it be wkb.SaveAs myFilename ??
NO, that would still be an empty workbook
Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
i could see this causing an error, but not the error yo specify
you still did not tell which line the error happens
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 24th, 2019, 04:10 AM
#20
Thread Starter
Addicted Member
Re: Save Workbook
Originally Posted by westconn1
NO, that would still be an empty workbook
i could see this causing an error, but not the error yo specify
you still did not tell which line the error happens
Its bizarre. I run the code and it just breaks. It doesn't highlight the line it breaks on. Has that happened before?
-
May 24th, 2019, 04:18 AM
#21
Re: Save Workbook
Has that happened before?
not uncommon in VBA, you need to step through code line by line til you find the line that errors
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 24th, 2019, 08:05 AM
#22
Thread Starter
Addicted Member
Re: Save Workbook
Code is breaking with a run time error '1004
Application-defined or object defined error
Breaks on this line....ActiveWorkbook.SaveAs myfilename
I forgot to mention that I don't want to change the name of my current file. Only the file that I'm saving down in to my folder.
Sub Saveworkbookset()
Dim Thiswb As Workbook
Set Thiswb = ActiveWorkbook
Workbooks.Add
myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
Debug.Print myfilename
ActiveWorkbook.SaveAs myfilename
-
May 24th, 2019, 08:59 AM
#23
Re: Save Workbook
What is the content of filename as printed in the Debug.Print ??
-
May 24th, 2019, 05:25 PM
#24
Re: Save Workbook
once you saveAs that is then the current file (active workbook) the file before saving is closed without changes
maybe you want saveCopyAs
myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
if the current file is not an .xlsm, that could cause an error, all previous indications showed the file to .xls, if you want to change the file type yo would need to specify the file type
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 28th, 2019, 04:14 AM
#25
Thread Starter
Addicted Member
Re: Save Workbook
Hello.
Apologies for late replay.
Changed the code to the below and still not working.
Error message on last line.. Run time error 1004.
Still not saving a copy of the work book to the drive I want. :-(
Sub Saveworkbookset()
Dim Thiswb As Workbook
Set Thiswb = ActiveWorkbook
Workbooks.Add
myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
Debug.Print myfilename
ActiveWorkbook.SaveCopyAs
End Sub
-
May 28th, 2019, 05:47 AM
#26
Re: Save Workbook
as posted in #23 what is the result in the immediate window?
see the note in the previous post about the file type!!
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 28th, 2019, 08:06 AM
#27
Thread Starter
Addicted Member
Re: Save Workbook
Originally Posted by westconn1
as posted in #23 what is the result in the immediate window?
Sorry. Don't know what you mean by immediate window?
see the note in the previous post about the file type!!
Current file is .xlsm and I want to save as a .xls
-
May 28th, 2019, 04:28 PM
#28
Re: Save Workbook
Code:
Current file is .xlsm and I want to save as a .xls
then you need to specify the file type when saving
if it was working the code as posted in #25 would try to save an empty workbook with no file name specified and no path information in the filename variable, that is not used
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 30th, 2019, 05:55 AM
#29
Thread Starter
Addicted Member
Re: Save Workbook
ok I've been testing this code....
Sub Savev1()
Dim Thiswb As Workbook
Set Thiswb = ActiveWorkbook
Workbooks.Add
myFilename = myPath & Range("A1").Value & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
ActiveWorkbook.SaveCopyAs Filename:=myFilename
End Sub
But it just opens a new work... Why doesn't it save it with the specified filename?
-
May 30th, 2019, 06:19 AM
#30
Re: Save Workbook
i tested your exact code, it opened a new workbook, then saved the copyas file (" Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm" with leading space as specified in the code) into documents folder (default folder, for me, when path not supplied), exactly as specified in the code
in this case, both mypath and range A1 are empty values
i tested in excel 2010 windows 10
probably for some reason you think the code should be doing something different to this
also as mentioned above if you saveAs or saveCopyas without specifying the file type it will save a .xlsx or .xlsm, regardless of if .xls is used in filename, this will give warning whenever the file is opened
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 30th, 2019, 08:06 AM
#31
Thread Starter
Addicted Member
Re: Save Workbook
Ok sorry there was a typo in my coding.
I can now see a file saved down in my folder. However its still saving down an empty work book. Do I need to specify what worksheets to save?
Also, as well as saving down a file to the folder, it also opens a new blank workbook. Do you know why its doing this?
Originally Posted by westconn1
i tested your exact code, it opened a new workbook, then saved the copyas file (" Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm" with leading space as specified in the code) into documents folder (default folder, for me, when path not supplied), exactly as specified in the code
in this case, both mypath and range A1 are empty values
i tested in excel 2010 windows 10
probably for some reason you think the code should be doing something different to this
How do I get around the warning message?
also as mentioned above if you saveAs or saveCopyas without specifying the file type it will save a .xlsx or .xlsm, regardless of if .xls is used in filename, this will give warning whenever the file is opened
-
May 30th, 2019, 04:27 PM
#32
Re: Save Workbook
Do I need to specify what worksheets to save?
NO, you just added a workbook then save so the empty workbook is the active book, i have no idea why you add a workbook as part of the code
it also opens a new blank workbook
remove the line that adds the empty workbook
How do I get around the warning message?
specify the file format when saving, to match the extension, just do a search on excel fileformat to get the list of constants
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 31st, 2019, 06:30 AM
#33
Thread Starter
Addicted Member
Re: Save Workbook
Ok finally got it sorted. Thank you for your patience and help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|