2

Could you please let me know how to revise the below code so that it opens it automatically?

 Range("A1:AK1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
    Workbooks.Open Filename:= _
        "Y:\DentalConsumables\Company\DMC\DMC - Planning & Materials\Build Plan DTW Reports\DataWHSreports\2015-03-26_DWHS_PEN EU Fixed.xlsx" _
        , UpdateLinks:=0, Notify:=False
    ActiveWindow.SmallScroll Down:=12
Community
  • 1
  • 1
Jason
  • 23
  • 6

1 Answers1

1

If only the date part changes, you can give this a try:

Edit1: Closing the opened file

Dim curDate As String, Fname As String
curDate = Format(Date, "yyyy-mm-dd") ' returns the current date in specified format
Dim wb As Workbook ' add a variable to pass your workbook object

Fname = "Y:\DentalConsumables\Company\DMC\DMC - Planning & Materials\" _
        & "Build Plan DTW Reports\DataWHSreports\" & curDate 
        & "_DWHS_PEN EU Fixed.xlsx"
Set wb = Workbooks.Open(FileName:=Fname, UpdateLinks:=False, Notify:=False)

' ~~> Other cool stuff goes here

wb.Close False ' Close the opened workbook; False indicates to not save changes
L42
  • 19,427
  • 11
  • 44
  • 68
  • Thank you very much. This worked great. Another question: could you please explain why the first "&" was necessary: Planning & Materials\" _ & "Build Plan DTW Reports\DataWHSreports\" & curDate. Also, how would I code it if I want to close the opened file? Thanks again – Jason Apr 03 '15 at 07:33
  • @Jason It is not really necessary. I just did it in two lines instead of one to make it readable on my browser (no scroll bars). As for your follow-up, see my edit. – L42 Apr 05 '15 at 22:02
  • Thanks to you, I am getting a hang of it. Closing worked flawlessly. Could you please help me close this final file where I used "like" to open it. Thanks again. – Jason Apr 07 '15 at 04:55
  • also, how would I have posted the above code as a code, like my original post? – Jason Apr 07 '15 at 17:00
  • @Jason You can post another question for that. Also you might want to [check this out](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – L42 Apr 07 '15 at 21:49
  • Thank you for that link. Very helpful. Got rid of all the activesheets, activerange,etc. Only remaining ones are the activeworkbooks. That is next. Thanks again. – Jason Apr 08 '15 at 05:02
  • @Jason You might want to see [accepting answers](http://stackoverflow.com/help/someone-answers) as well as one way of saying thank you as explained [here](http://stackoverflow.com/tour) as well. Go back your previous questions as well and check if you got a good answer you can mark. – L42 Apr 08 '15 at 05:04