VBA-Excel: Create worksheets with Names in Specific Format/Pattern. VBA-Excel: Read XML by Looping through Nodes. PriorDate = Format(Range(“B6”), “yyyy-mm-dd”)Įnter your email address to subscribe to this blog and receive notifications of new posts by email. PrevDate = Format(Range(“B5”), “yyyy-mm-dd”) LastDate = Format(Range(“B4”), “yyyy-mm-dd”) Is it possible it was moved, renamed or deleted?” – even though the file does exist, and version 1 can successfully open the file):ĭim CurrDate, LastDate, PrevDate, PriorDate As String Version 2 (accounts for weekends, but won’t open the spreadsheet – I get the following error message: “Sorry, we couldn’t find ‘Q:\Transfer\June\VBA\_Outages.xlsx”. Workbooks.Open Filename:=FilePath & PrevDate & “_Outages.xlsx”ĪctiveWorkbook.SaveAs Filename:=FilePath & LastDate & “_Outages.xlsx” PriorDate = Format(DateAdd(“w”, -3, CurrDate), “yyyy-mmm-dd”) PrevDate = Format(DateAdd(“w”, -2, CurrDate), “yyyy-mmm-dd”) LastDate = Format(DateAdd(“w”, -1, CurrDate), “yyyy-mmm-dd”) Version 1 (does not account for weekends):ĭim CurrDate, LastDate, PrevDate, PriorDate There are several tabs within the spreadsheet – I copy the most recent, delete the prior day’s tab (2014-Jan-03), and create a new tab with all of the formulas I need to process today’s work (today is really yesterday, as we’re reconciling data posted yesterday). Please correct me if my understanding is correctĮach day I’m trying to copy data from an existing spreadsheet (2014-Jan-06_Outages.xlsx) into a new version of the existing spreadsheet (2014-Jan-07_Outages.xlsx). I am not saying this a best solution but still can be used as a work around. MainWorkBook.Sheets(“Sheet1”).Range(“B” & intCounter) = strDay 
MainWorkBook.Sheets(“Sheet1”).Range(“A” & intCounter) = i
If strDay “Saturday” And strDay “Sunday” Then
Ms excel for mac dateadd code#
I just came up with the small piece of code for this…įor i = Date To DateAdd(“m”, 1, Date) ‘ your specific period – from the current date, you want to do a task on each day, till the specified period (excluding weekends). Yes, i checked both “W” and “D” are working in same manner.īut still we can do something about the objective you want to achieve.
0 kommentar(er)
