Copy Paste through VBA with changing file name

Question

At my work I manually copy sales forecast into another file that gets uploaded to our website. The two files I manually copy have the same exact layout everytime (no cells are changing positions) but the name of the file changes due to having different products every day.

I tried the VBA record button but since the file changes its name everyday then it becomes useless, since it would require me to change the name of the file in every sentence. Can I somehow define the name of the file in the beginning so I only have to change that when I run the macro?

    Sub Test2()
' Test2 Macro

Dim FileName As String
FileName = ""

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select File"
    .Filters.Add "Excel File", "*.xls?"
    .AllowMultiSelect = False

    If .Show Then
        FileName = .SelectedItems(1)
    End If
End With
If Len(FileName) < 4 Then Exit Sub 'No file selected

Dim TempWorkbook As Workbook
Set TempWorkbook = Workbooks.Open(FileName, ReadOnly:=True)

ActiveSheet.Range("U8").FormulaR1C1 = "=" & TempWorkbook.Worksheets("FINAL FORM").Cells(18, 2).Address(True, True, xlR1C1, True)

TempWorkbook.Close SaveChanges:=False
Set TempWorkbook = Nothing

End Sub

For example somehow define [MNY FDL CS Lifter Lip.xlsx] to "wb1" and then have that in the following sentences so I only have to change the name in the define sentence?

This sounds very doable. Can you [edit] your question with any code you have so far? This will help us help you. — BigBen, Sep 16 '19 at 15:07
Can you give some obfuscated example filenames (e.g. use "Product1" and "Product2" or "CompanyA" instead of real Product/Company names!) - if there is a consistent format to the name (e.g. "Product1_Product2_YYYYMMDD_Company_UserName.xlsx"), then you can use [the `Dir` function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function) with Wildcards... — Chronocidal, Sep 16 '19 at 15:31
@christian is your workbook in the same folder than the others workbook? — TourEiffel, Sep 16 '19 at 15:41
Thanks so much for the responses! I am truly grateful. @Chronocidal I tried to edit the code with the correct names — Christian, Sep 16 '19 at 15:50
Is there any logic whith the choose of the cells ? Or it will always be A8 B8 U9 A9 — TourEiffel, Sep 16 '19 at 15:54
(At this point, I will note that the "quick" method would be to define a `String` with the Workbook name in it: `Dim WorkbookName AS String: WorkbookName = "MNY FDL CS Lifter Lip.xlsx"`, and then use that in the formula: `ActiveCell.FormulaR1C1 = "='[" & WorkbookName & "]FINAL FORM'!R18C9"`, so you only have to change 1 value) — Chronocidal, Sep 16 '19 at 15:56
@Dorian It will always be the same cells. I have 25 sales forecast (the one above is just 1) but its in a template where the cells position will never change for the 25 forecasts. — Christian, Sep 16 '19 at 15:56

score 0 · Answer 1 · answered Sep 16 '19 at 15:32

0

First question is "How do you know what to name the new file?" You need to load that into a variable and use that as the file name following the instructions given in this SE Q&A - How to properly write and save a new Excel file?

Some more code for you to crib from MS - https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa221273(v=office.11)?redirectedfrom=MSDN

answered Sep 16 '19 at 15:32

Barry

63
7

Thank you so much for taking the time to respond! – Christian Sep 17 '19 at 07:53

Chronocidal · Accepted Answer · 2019-09-17T14:36:44.550

0

First, let's tidy your recorded code up slightly. This will make everything else easier later:

Sub Test2()
' Test2 Macro
    ActiveSheet.Range("I don't know what goes here!").FormulaR1C1 = "='[test2.xlsx]FINAL FORM'!R18C9"
    ActiveSheet.Range("S8").FormulaR1C1 = "='[test2.xlsx]FINAL FORM'!R18C2"
    ActiveSheet.Range("B8").FormulaR1C1 = "='[test2.xlsx]FINAL FORM'!R13C3"
    ActiveSheet.Range("A8").FormulaR1C1 = "='[test2.xlsx]FINAL FORM'!R17C3"
    'Range("A9").Select
End Sub

(Always try to avoid using Select in Excel VBA if possible)

Next, let's take one line - ActiveSheet.Range("S8").FormulaR1C1 = "='[test2.xlsx]FINAL FORM'!R18C2" and work out how to replace [test2.xlsx] with something more code-friendly. The "quick" way to do this would be to just store it in a String, like this:

Dim WorkbookName AS String
WorkbookName = "='[test2.xlsx]FINAL FORM'"
ActiveSheet.Range("S8").FormulaR1C1 = WorkbookName & "!R18C2"

This means that you only need to change one line of code, and everything else changes to match. But, this is far from the best option.

A better choice would be to assign the Workbook to a Workbook object, and to use FileDialog to open it. It we start with the FileDialog code to get the File (this will make a Pop-Up to select the file)

Dim FileName AS String
FileName = ""

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select File"
    .Filters.Add "Excel File", "*.xls?"
    .AllowMultiSelect = False

    If .Show Then
        FileName = .SelectedItems(1)
    End If
End With
If Len(FileName) < 4 Then Exit Sub 'No file selected

Then we open the Workbook up...

Dim TempWorkbook AS Workbook, CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet 'Store the ActiveSheet, it will change
Set TempWorkbook = Workbooks.Open(FileName, ReadOnly:=True)

This lets us use the Workbook, Worksheet and .Address:

CurrentSheet.Range("S8").FormulaR1C1 = "=" & TempWorkbook.Worksheets("FINAL FORM").Cells(18, 2).Address(True,True,xlR1C1,True)

(Or, if you only want the Value, without the Formula)

CurrentSheet.Range("S8").Value = TempWorkbook.Worksheets("FINAL FORM").Cells(18, 2).Value

Finally, once we're finished, we can close the WorkBook, and tidy up:

TempWorkbook.Close SaveChanges:=False
Set TempWorkbook = Nothing
Set CurrentSheet = Nothing

edited Sep 17 '19 at 14:36

answered Sep 16 '19 at 16:06

Chronocidal

6,827
1
12
26

Thank you so much for taking the time to respond! I will test it out. – Christian Sep 17 '19 at 07:54
Hi @Chronocidal I tried your solution and it works really well apart from ActiveSheet.Range("U8").FormulaR1C1 = "=" & TempWorkbook.Worksheets("FINAL FORM").Cells(18, 2).Address(True, True, xlR1C1, True) It doesn't give me any values. I edited my code if you want to see. Thanks – Christian Sep 17 '19 at 14:21
@Christian Please do **not** edit code in the question: it makes it useless for anyone with the same issue who is searching, and it leads to confusion as to why the other answers are "invalid". If you need to, add it at the bottom of the question as an addendum. That said: Are you getting any particular error, or is it just not pulling through the value you want? Have you checked what the Formula in the Cell shows at the end of the code? – Chronocidal Sep 17 '19 at 14:25
Oh im sorry for that. I was told in another comment to edit my code in the question. I am not getting any values. I reckon ActiveSheet.Range("U8") is the document I working in and TempWorkBook.Worksheets("Final Form").Cells(18,2) would be row 18 and column B which is the cell its getting extracted from? – Christian Sep 17 '19 at 14:32
@Christian Adding to the question is good, but always be leery of *deleting* things unless absolutely necessary. Correct, those are the cells - however, I just realised that opening the workbook will change the `ActiveSheet`! I have edited the code to solve that – Chronocidal Sep 17 '19 at 14:37
youre an absolute star! Thank you so much. Last and final question. I have about 80 cells in one box (which is one product) and 20 products leading to 1.600 cells. Is there a way to update the sheet faster than doing one formula line for all 1600? – Christian Sep 17 '19 at 14:43
@Christian Are the cells all in a big rectangle? If so, you can set the Formula for the whole Range (`CurrentSheet.Range("S8:E88").FormulaR1C1`) at once - but, you will need to use `Address(False,False,xlR1C1,True)` instead of `Address(True,True,xlR1C1,True)`. But, setting `.Value` instead of `.Formula`/`.FormulaR1C1` will typically be faster (although it "locks" the value in place) – Chronocidal Sep 17 '19 at 14:48
Hi again. I created my code which is very very long. Now it says "Procedure Too Large". I tried to put in some "End Sub Sub Proc1()" However, that just makes it say my first line in the code is wrong? – Christian Sep 18 '19 at 15:18
@Christian You need to make sure that every Subroutine is complete and valid in its own right - if nothing else, it makes it easier to test/fix! Especially if something is repetitive, you should be looking to turn it into a separate Subroutine. If you need help breaking it down, you could always try [codereview.se] - but first, see how much you can remove by [Avoiding using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) - you can often reduce as many as 5 lines to just 1! – Chronocidal Sep 18 '19 at 15:28
Hi again, I added to my post with an edit with two sections of current sheet code. How would you add a formula for it to do this? Thanks for saving my life once again! – Christian Sep 18 '19 at 15:34
@Christian That sounds like it should probably be posted as [a new question](https://stackoverflow.com/questions/ask) instead – Chronocidal Sep 18 '19 at 15:37

Copy Paste through VBA with changing file name

2 Answers2