open file (as variable) from macro

[Guest]

User inputs a date etc., I add path in front of date and some other
manipulation, and come up w/ the filename that I'm trying to open with a
macro. Then I work on that file, print it, etc. and want to close it. Problem
is, when I record the "open filename", it puts that one filename in my macro
as literal, rather than the cell address. I need that to be variable. As is,
even when a different date is entered by user, it still keeps opening the
same file cuz it's recorded that way in the macro.

 

[Dave Peterson]

Maybe something like:
Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim mystr As String
Dim userDate As Date
Dim teststr As String

userDate = DateSerial(2005, 12, 31)

mystr = "C:\my documents\excel\test\filePrefix" _
& Format(userDate, "yyyy_mm_dd") & ".xls"

teststr = ""
On Error Resume Next
teststr = Dir(mystr)
On Error GoTo 0

If teststr = "" Then
MsgBox "File not available"
Exit Sub
End If

Set wkbk = Workbooks.Open(Filename:=mystr)
wkbk.Worksheets(1).PrintOut

wkbk.Close savechanges:=False

End Sub