Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by Seamus2626
at 2024-07-22 01:03:07
ModifyPoint:500 Replies:5 POST_ID:829067USER_ID:11690
Topic:
Microsoft Excel Spreadsheet Software;;
So, i have a file zyz.xls
its store in
Q:DebtInputs2014201406Reporting
Can i get some code that looks for the folder from last month, so the process is always ran after month end. So in august, we run July files, so the code would be looking for
Q:DebtInputs2014201407Reporting
Is this possible through a date function?
Thanks
its store in
Q:DebtInputs2014201406Reporting
Can i get some code that looks for the folder from last month, so the process is always ran after month end. So in august, we run July files, so the code would be looking for
Q:DebtInputs2014201407Reporting
Is this possible through a date function?
Thanks
Expert: duncanb7 replied at 2024-07-23 08:36:47
if so, why others will use DateAdd() on VBA ?
Actually I guess we can use other way to do that without Month() and DateAdd() assist if we want to , Right ?
Anyway, just reminder and note only
Duncan
Actually I guess we can use other way to do that without Month() and DateAdd() assist if we want to , Right ?
Anyway, just reminder and note only
Duncan
Author: Seamus2626 replied at 2024-07-23 08:32:22
Hi Duncan,
Ive beeen using the following
Sub OpenFileCross_Pipeline_Staff()
FileYear = Year(Date)
MonthOffset = 0
If Day(Date) = 1 Then MonthOffset = 1
FileMonth = Month(Date) - MonthOffset
FileDate = Format(Date - 1, "yyyymm")
FilePath = "R:SPMMonthly_Inputs" & FileYear & "" & FileDate & "ReportingPCM_Pipeline_Analysis_Staff_raw.xlsx"
Workbooks.Open (FilePath)
End Sub
Thanks for your add
Ive beeen using the following
Sub OpenFileCross_Pipeline_Staff()
FileYear = Year(Date)
MonthOffset = 0
If Day(Date) = 1 Then MonthOffset = 1
FileMonth = Month(Date) - MonthOffset
FileDate = Format(Date - 1, "yyyymm")
FilePath = "R:SPMMonthly_Inputs" & FileYear & "" & FileDate & "ReportingPCM_Pipeline_Analysis_Staff_raw.xlsx"
Workbooks.Open (FilePath)
End Sub
Thanks for your add
Expert: duncanb7 replied at 2024-07-23 08:07:49
Any comment , Seamus2626 & GasperK ?
Please advise
Duncan
Please advise
Duncan
Expert: duncanb7 replied at 2024-07-22 02:50:08
Note:
Just for reminder and reference only, It should use DateAdd() for previous month and previous year when the Month(Date) is equal 1, and Month(Date)-1 that will be 0 for January for previous month
I try this example date at Range("A1")="23/01/2014", please see the code based on previous post code and code for Format(DateAdd("m", -1, Range("A1")), "mm") for previous month.
Duncan
Just for reminder and reference only, It should use DateAdd() for previous month and previous year when the Month(Date) is equal 1, and Month(Date)-1 that will be 0 for January for previous month
I try this example date at Range("A1")="23/01/2014", please see the code based on previous post code and code for Format(DateAdd("m", -1, Range("A1")), "mm") for previous month.
Duncan
Sub test() Dim XYear As String Dim Xmonth As String Dim Xmonth2 As String Dim MyDate As String Dim str As String Dim tmp As String Dim res As String Range("A1") = "23/01/2014" Range("A1").NumberFormat = "dd/mm/yyyy" tmp = DateValue(Range("A1")) XYear = Year(tmp) Xmonth = Month(tmp) - 1 Xmonth2 = Format(Xmonth, "00") MyDate = XYear & Xmonth2 Debug.Print "====================" Debug.Print MyDate If Month(tmp) = 0 Thenres = "File-1:Q:DebtInputs" & Year(Range("A1")) & "" & Year(Range("A1")) & Format(DateAdd("m", -1, tmp), "mm") & "Reporting" Elseres = "File-2: Q:DebtInputs" & Format(DateAdd("yyyy", -1, Range("A1")), "yyyy") & "" & Format(DateAdd("yyyy", -1, Range("A1")), "yyyy") & Format(DateAdd("m", -1, Range("A1")), "mm") & "Reporting" End IfDebug.Print res End Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:
Accepted Solution
Expert: GasperK replied at 2024-07-22 01:14:47
500 points EXCELLENT
Here's what you need
sub test() XYear = Year(Date) XMonth = Month(Date) - 1 XMonth2 = Format(XMonth, "00") MyDate = XYear & XMonth2 MsgBox MyDate 1:2:3:4:5:6:7:
End Sub
