How to make this code not rely on the file name

Question

I have this code that pulls data from 4 separate workbooks and paste them into the next empty section in a template workbook (FRF_Data_Macro_Insert_Test). This works perfectly but i have one issue, I need it to be able to paste in the active workbook and not to be dependent on the file name. Because this is a template and therefore read only, it prompts you to save as a different file name upon opening. I told the people using this to just cancel the first save as window and just save as when all done pulling data but they keep saving as before they pull data making it not work because its looking for FRF_Data_Macro_Insert_Test filename. Any help is much appreciated! Thanks

Code:

Sub DataTransfer()

  Const FPATH As String = "C:\Users\aholiday\Desktop\FRF_Data_Macro_Insert_Test\"

  Application.ScreenUpdating = False

Dim wb As Workbook
Dim shtAlpha As Worksheet 'Template
Dim locs, loc
Dim rngDest As Range

locs = Array("Location1.xls", "Location2.xls", _
             "Location3.xls", "Location4.xls")

Set shtAlpha = Workbooks("FRF_Data_Sheet_Template.xlsm").Sheets("DataInput")

'set the first data block destination
Set rngDest = shtAlpha.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(5, 3)

For Each loc In locs

    Set wb = Workbooks.Open(FileName:=FPATH & loc, ReadOnly:=True)

    rngDest.Value = wb.Sheets("Data").Range("I3:K7").Value

    wb.Close False

    Set rngDest = rngDest.Offset(0, 3) 'move over to the right 3 cols

Next loc

Application.ScreenUpdating = True

End Sub

You'd have to tell them to rename it to FRF_Data_Sheet_Template_2.xlsm or something which still holds a resemblance. Then you loop through the directory and look for a file with that name. I think it's the only way. — David G, Aug 14 '15 at 13:07
You can save your template file as a template file type (.xltm/.xltx) so when it opens, it opens as a copy — Dave, Aug 14 '15 at 13:19

score 1 · Accepted Answer · answered Aug 14 '15 at 14:06

As your macro is in the workbook you want to reference, you can simply use ThisWorkbook:

Sub DataTransfer()

Const FPATH As String = "C:\Users\aholiday\Desktop\FRF_Data_Macro_Insert_Test\"

Application.ScreenUpdating = False

Dim wb As Workbook
Dim shtAlpha As Worksheet 'Template
Dim locs, loc
Dim rngDest As Range

locs = Array("Location1.xls", "Location2.xls", _
             "Location3.xls", "Location4.xls")

Set shtAlpha = ThisWorkbook.Sheets("DataInput")

'set the first data block destination
Set rngDest = shtAlpha.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(5, 3)

For Each loc In locs

    Set wb = Workbooks.Open(Filename:=FPATH & loc, ReadOnly:=True)

    rngDest.Value = wb.Sheets("Data").Range("I3:K7").Value

    wb.Close False

    Set rngDest = rngDest.Offset(0, 3) 'move over to the right 3 cols

Next loc

Application.ScreenUpdating = True

End Sub

Something else isn't working now (not related to this). Let me figure this out first and then ill let you know if this works or not. Thanks Dave! — Duraholiday, Aug 14 '15 at 14:38

score 0 · Answer 2 · answered Aug 14 '15 at 13:24

0

I would post this as just a comment, but it won't let me.

I'm not sure if i'm following what you're asking right, but if it's a matter of just saving a separate copy with a different name automatically, then itt would be Workbooks("FRF_Data_Sheet_Template.xlsm").SaveCopyAs

answered Aug 14 '15 at 13:24

cyb3rwolf

13
3

This macro pulls data from 4 other files, and paste them into the template where the macro is. The macro looks for the template name in the code, if an employee opens, saves as different name it will not work. I need to make it not rely on the name of the file. Like copy from Location1 paste to active workbook and so on. Basically there is 4 locations per test and after completing each test they would run the macro then run another test and run the same macro in the same document and so on for x number of parts. I would like it to open, save as, and the macro to run regardless of the name. – Duraholiday Aug 14 '15 at 13:32
So are your users opening the template and making other changes to it before they run this code? – Dave Aug 14 '15 at 13:43
There is only 3 things they can do ( only one effects the code), everything else is locked and no select. They can paste their own data, use the macro to pull data automatically from the test results, and rename the workbook by Saving as because the file is read only. The Problem is sometime they change the name before they use the macro, again this macro looks for the template name so if they change it it errors out. I can not just change the name in the macro because they are supposed to save by what ever part or part program. So the only way i see this working is a code ActiveWorkBook – Duraholiday Aug 14 '15 at 13:57
Which workbook is the macro in? How about a dropdown option that gets the names of the open workbooks so they can choose the open template file? – Dave Aug 14 '15 at 14:00
Idk how to make that work in this code, The code way suggested to me by someome on here because my code wasn't working right http://stackoverflow.com/questions/31882912/script-runs-but-doesnt-do-anything – Duraholiday Aug 14 '15 at 14:01
There is 5 total workbooks that are in involved. The template and the 4 location workbooks, the location workbooks open in the ack ground and are used only to copy the data. The macro is in the template workbook, the only one that is open. And no cause there are an infinity names for parts because of prototypes and other random Test – Duraholiday Aug 14 '15 at 14:04
Maybe i should block the saving options completely and somehow have code prompt the save as window when they exit the template. Is that possible? – Duraholiday Aug 14 '15 at 14:06
Just answered for you, hope it helps :) – Dave Aug 14 '15 at 14:07

How to make this code not rely on the file name

2 Answers2