Copy range from closed variable workbook (variable determined by application.getopenfilename)

Question

I'm trying to copy a range from a closed variable workbook (dimmed as fName). Can't get it to work and don't seem to be finding my error. Code is as seen below, I personally think the problem is with the variable fName but maybe I'm looking in the complete wrong direction.

Sub LotoRipper()
Dim fName As String
fName = Application.GetOpenFilename
Workbooks.Open fName
Workbooks(fName).Worksheets("LoTo Sleutellijst").Range("E13").Copy _
Workbooks("LOTO Sleutellijst O-M_rev4.3.xlsm").Worksheets("LoTo Sleutellijst").Range("E13")

End Sub

Trying to learn from this, so please explain what I did wrong instead of providing a solution, if possible.

You may also want to see [ExecuteExcel4Macro to get value from closed workbook](https://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook/9261915#9261915) — Siddharth Rout, Aug 07 '18 at 07:47
Shame that that refers to a non-variable Workbook to extract data from. — RobExcel, Aug 07 '18 at 08:56
This code refers to fName (as a variable from which workbook to take the data from), that one doesn't. — RobExcel, Aug 07 '18 at 08:57
`fName` gives you something like `C:\Temp\MyFile.xlsx`. From that I am sure you can get `C:\Temp\ ` and `MyFile.xlsx`? And in that link simply get those values in `wbName` and `wbPath` :) — Siddharth Rout, Aug 07 '18 at 09:19
Fname is a variable which I use to keep the worksheet I'm transforming variable. As in, this code is used to run in a loop to open about 2k worksheets and transforming them. fName is just the dim i used on the Variable. — RobExcel, Aug 08 '18 at 09:06

score 1 · Accepted Answer · answered Aug 07 '18 at 06:42

You are correct - in your variable you have a whole path to your workbook. Once you have opened it you should get it name for eg:

Sub testMacro()
Dim fName As String
fName = Application.GetOpenFilename
Workbooks.Open fName
fName = ActiveWorkbook.Name '<--- try this
Workbooks(fName).Worksheets("sheet1").Range("E13").Copy _
Workbooks("test2.xlsx").Worksheets("sheet1").Range("E13")

End Sub

Copy range from closed variable workbook (variable determined by application.getopenfilename)

1 Answers1