Import button based off cell value = file name

[jamesraywebber]

So I have a report that is emailed to me daily, I have a macro enabled to save the files automatically to a location on my desktop (C:\Users\rwebber\Desktop\TSA Hourly Report) and the file saves as SSC Hourly Update "the date it was emailed". I would like to place a button on my page called "TSA Daily" so that all I have to do is click on it and it would copy all the info from "sheet1" to the sheet named "sheet2" in my new workbook.

The only issue I am having is that at the top cell B1 on TSA Daily had the date. So is it possible to have the formula or macro look in cell B1 on TSA daily to get the rest of the file name in C:\Users\rwebber\Desktop\TSA Hourly Report? (Ex. today is 11/20/2017, it would look for the file name "SSC Hourly Update 11-20-2017" and copy that "sheet1" over to "sheet2" in the "TSA Daily Email Template"? Any help would be greatly appreciated!

 

[AFPathfinder]

So, you are having a date formatting issue with the import macro?

Workbooks.Open "C:\Users\" & Environ("USERNAME") & "\Desktop\TSA HourlyReport\SSC Hourly Update " & Format(ActiveWorkbook.Sheets("sheet1").Range("B1").Value, "m-d-yyyy") & ".xlsx"

 

[jamesraywebber]

Well not really good with macros or script but I want a button or if it can pull the data automatically on a closed workbook. I have a file named SSC Horly Update “to days date” that automatically saves once in comes through outlook on my desktop. In my sheet TSA Hourly Email sheet, I want it to update from the SSC Hourly email sheet1 to the cells on sheet2 in TSA Hourly Email workbook

 

[AFPathfinder]

Are you familiar with creating a button and assigning a macro to it? Even if we provide a macro, you'll need to install and configure it.

The macro itself sounds like it would be pretty easy. My initial thought would be to copy the whole sheet over and not just the cells, but I have some questions. Are the workbooks of the same extension (xls or xlsm)? Does it matter if the copied sheet is named "sheet2" when the macro is finished? Is there a need to run the macro again in the same workbook where you might need to delete old sheets before bringing in the new sheet?

 

[jamesraywebber]

Yes I know how to setup a button and assign a macro. Just not sure how to write it. The SSC Hourly Update "date" is xlsx file and my file TSA Daily Email is a xlsm file. The name of the sheet can stay sheet2. The only other thing I would like to do is make another button to paste stuff into a email template called TSA Daily Email.oft

 

[AFPathfinder]

I'll see about getting you a macro written up shortly.

The pasting into an OFT email template is something I haven't tried before. I usually hardcode the OFT body text into the macro so I don't have to use the OFT file (not a good practice). However, if you use Bookmarks in the OFT to mark where the info is supposed to go, I think you should be able to programmatically reference the bookmarks to insert the data. I've used bookmarks to fill Word forms, so I think it should work with Outlook OFTs.

 

[AFPathfinder]

Here's an attempt at the macro. I haven't tested this, so there maybe an error or two. The sheet names may be an issue with the macro as it's not accounting for duplicate naming errors.

Sub SheetImport()
    ' Open workbook who's date matches the date in cell B1 of this workbook.
    Workbooks.Open "C:\Users\" & Environ("USERNAME") & "\Desktop\TSA HourlyReport\SSC Hourly Update " _
        & Format(ActiveWorkbook.Sheets("sheet1").Range("B1").Value, "m-d-yyyy") & ".xlsx"
        
    ' Copy the whole sheet over to this workbook after the first sheet.
    Sheets("sheet1").Copy After:=ThisWorkbook.Sheets(1)
    
    ' Close the daily workbook without saving changes.
    Workbooks("SSC Hourly Update" & Format(ThisWorkbook.Sheets(1).Range("B1").Value, "m-d-yyyy") _
        & ".xlsx").Close SaveChanges:=False
        
    'Rename the copied sheet to "sheet2".
    Sheets("sheet1").Name = "sheet2"
    
    MsgBox "Done."
End Sub