So, I was trying to skip this portion of a project I'm working on, and using an Input box to capture the right month. But based on my own typo in testing, I figured I'd try to snag the date from the filename instead, and then just have user verify or change if needed.
So... I start with files like this:
"MyFancyFileName-[highlight #FCE94F]APR 15[/highlight] And some other gibberish.xlsx"
I am basically cleaning the data up a bit into a more normalized fashion in a new workbook (consolidating several other workbooks - each workbook for a month). Basically, instead of copying and pasting, b/c of all their variations of what columns are, or are not, included, I'm grabbing the values one at a time, and adding them to a normalized worksheet in a new workbook.
So that said.. all the above is working except for grabbing the month and year correctly from the file name.
Without further ado, here's what I've got so far, and I've got a feeling that surely I've ALMOST got it. I'll be testing on my end in case I don't get a response here.
[CODE VBA]
Dim strMonth As String
Dim wb As Workbook
[green]'Loop through files that are workbooks that I want to include...[/green]
strMonth = Mid(wb.Name, InStr(wb.Name, "-") + 1, 6)
Debug.Print strMonth
[green]'At this point, I get APR 15 in immediate window for a file that has APR 15 in the file name string.[/green]
[highlight #FCE94F]'[green]The following line is where I'm getting the error: Current error is "Run-time error 13: Type mismatch"[/green][/highlight]
strMonth = Month(DateValue(Left(strMonth, 3) & "1, " & Right(strMonth, 4)))
Debug.Print strMonth
[green]'Input box to verify the year and month or whatever I grab is correct[/green]
strMonth = InputBox( _
"Please enter Year and Month of Workbook in YYYYMM format." & vbCrLf & _
"For example, this month is " & Format(Date, "YYYYMM") & ".", "Enter Workbook Month" _
, strMonth) ' Format(DateAdd("m", -1, Date), "yyyymm")[/CODE]
I'm sure the issue is that I'm not getting the date format lined up in a way that Excel can recognize as a date. Can anyone give me any pointers? I'm trying to do this without a case statement for the month abbreviations. I figure there has to be a smarter and shorter way.
Thanks in advance for any help
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
So... I start with files like this:
"MyFancyFileName-[highlight #FCE94F]APR 15[/highlight] And some other gibberish.xlsx"
I am basically cleaning the data up a bit into a more normalized fashion in a new workbook (consolidating several other workbooks - each workbook for a month). Basically, instead of copying and pasting, b/c of all their variations of what columns are, or are not, included, I'm grabbing the values one at a time, and adding them to a normalized worksheet in a new workbook.
So that said.. all the above is working except for grabbing the month and year correctly from the file name.
Without further ado, here's what I've got so far, and I've got a feeling that surely I've ALMOST got it. I'll be testing on my end in case I don't get a response here.
[CODE VBA]
Dim strMonth As String
Dim wb As Workbook
[green]'Loop through files that are workbooks that I want to include...[/green]
strMonth = Mid(wb.Name, InStr(wb.Name, "-") + 1, 6)
Debug.Print strMonth
[green]'At this point, I get APR 15 in immediate window for a file that has APR 15 in the file name string.[/green]
[highlight #FCE94F]'[green]The following line is where I'm getting the error: Current error is "Run-time error 13: Type mismatch"[/green][/highlight]
strMonth = Month(DateValue(Left(strMonth, 3) & "1, " & Right(strMonth, 4)))
Debug.Print strMonth
[green]'Input box to verify the year and month or whatever I grab is correct[/green]
strMonth = InputBox( _
"Please enter Year and Month of Workbook in YYYYMM format." & vbCrLf & _
"For example, this month is " & Format(Date, "YYYYMM") & ".", "Enter Workbook Month" _
, strMonth) ' Format(DateAdd("m", -1, Date), "yyyymm")[/CODE]
I'm sure the issue is that I'm not getting the date format lined up in a way that Excel can recognize as a date. Can anyone give me any pointers? I'm trying to do this without a case statement for the month abbreviations. I figure there has to be a smarter and shorter way.
Thanks in advance for any help
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57