open file (as variable) from macro
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. |
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 d chaps wrote: 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 |
All times are GMT +1. The time now is 09:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com