Insterting a Date Variable into a referenced Worksheet file path

[jbumps]

First Post!

I'm brand new to VBA and what I know is from reading part of a book, so please bear with me.

I have a workbook that is modifed and saved on a daily basis, with an exerpt from my code below:

Windows("Yields_Bulk_CY10Daily_12.17.2012.xls").Activate

I'd like to do the following: Insert a variable into the path that would dictate the date portion. i.e. x = xx.xx.xxxx I understand that I can simply open it every day and dictate the day that way, but eventually this will be handed off and I don't want a user in the editor playing with the code.

Ultimately, I'll creat a pop-up that will provide the user the entry point, where "x" will be defined.

Can anyone provide some input on how this can be accomplished?

If needed, the entire string of code is below:

Sub SAPYieldDataImport()

Msg = "Import Yield Data from SAP?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
MsgBox "Data Not Imported. No Changes Made."
Else

Windows("Worksheet in ALVXXL01 (1)").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows("Yields_Bulk_CY10Daily_12172012.xls").Activate
Sheets("data drop").Select
Range("A2").Select
ActiveSheet.Paste
Calculate
Range("R2").Select
Application.CutCopyMode = False
Range("A1:R3000").Sort Key1:=Range("R2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End If

Sheets("Main").Select
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
Dim NRows As Long
NRows = Range("AQ2").Value
ActiveCell.Resize(NRows, 1).EntireRow.Insert

Sheets("Data Drop").Select
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("Main").Activate
Range("A2").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveSheet.Paste
Calculate

Range("S2").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Offset(-1, 0).Select

Range(ActiveCell, ActiveCell.Offset(0, 23)).Select

Selection.Copy
For x = 1 To Worksheets("Data Drop").Range("X2")
Range(ActiveCell.Offset(x, 0), ActiveCell.Offset(0, 23)).Select
ActiveSheet.Paste
Next x
Calculate

End Sub

 

[Kathy321]

a

 

[jon49]

You can play with this code to get what you want exactly. Let me know if you have any more questions!

Sub AddDate(ByVal wkb As Workbook)

Dim sDate As String 'Date as string
Dim sNewWkbName As String 'New workbook name

sDate = Month(today) & "." & Day(today) & "." & Year(today)

sNewWkbName = wkb.FullName & "_" & sDate
'OR
'Remove previous date and add new date
'sNewWkbName = Left$(wkb.FullName, InStrRev(wkb.FullName, "_")) & sDate

wkb.Name = sNewWkbName
wkb.Save
'OR
'wkb.SaveAs sNewWkbName

End Sub

 

[jbumps]

Thanks Jon. Let me give it a try.

 

[jbumps]

You can play with this code to get what you want exactly. Let me know if you have any more questions!

Sub AddDate(ByVal wkb As Workbook)

Dim sDate As String 'Date as string
Dim sNewWkbName As String 'New workbook name

sDate = Month(today) & "." & Day(today) & "." & Year(today)

sNewWkbName = wkb.FullName & "_" & sDate
'OR
'Remove previous date and add new date
'sNewWkbName = Left$(wkb.FullName, InStrRev(wkb.FullName, "_")) & sDate

wkb.Name = sNewWkbName
wkb.Save
'OR
'wkb.SaveAs sNewWkbName

End Sub

I'm not sure that my intentions behind the insertion of the variable were clear enough, after trying your suggestion. I don't want to save or alter a name of any file, I want the Macro to make a particular workbook (which will already be open) the active one. The issue arrises in that it's name, as outlined in my original post, changes each day.

Does that make better sense and/or would your suggestion still be applicable?

Thanks.

 

[jon49]

Yes, you're right, I misread what you posted. I'll update the code for you.

 

[jon49]

OK, I tested this code and it works. Of course, you will need the folder path added to the name also for it to work.

Sub Testopen()    
    OpenWorkbookByDate "Yields_Bulk_CY10Daily_.xls"
    
End Sub

Result:

Yields_Bulk_CY10Daily_12.18.2012.xls

Sub OpenWorkbookByDate(ByVal sFullName As String)    
    Dim iInsertDate As Integer
    Dim sDate As String 'Date as string
    Dim sNewWorkbookName As String
    
    sDate = Month(Now()) & "." & Day(Now()) & "." & Year(Now())
    
    iInsertDate = InStrRev(sFullName, ".")
    
    sNewWorkbookName = Left$(sFullName, iInsertDate - 1) & sDate & Right$(sFullName, Len(sFullName) - iInsertDate + 1)
    
    Application.Workbooks.Open sNewWorkbookName


End Sub