Application.GetOpenFilename

rlcurlee67

New Member
Joined
Jun 1, 2022
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
I have a macro using the application.getopenfilename and it works great at selecting the file. But, I need to actually open the file copy from it and close it.

[
Dim myFile As String
Dim YourFolderPath As Variant
Dim rng As Range
Dim B46 As Variant
Set rng = Range(ActiveCell, ActiveCell)

Sheets("2022 Oil Production").Select
YourFolderPath = "X:\UTCS\Region\USA\Assets\GOM_DEV\Julia\SubSrfc\Rsvr_Mgmt-Surv\Surv-Data\Prod\St. Malo Morning Report - by Prod Date\2022"
ChDir YourFolderPath
ChDrive "X"
myFile = Application.GetOpenFilename
MsgBox "open" & myFile
Range("B46").Select
Selection.Copy
Windows("Project 3. Oil Production copy (003).xlsx").Activate
ActiveSheet.Paste
Windows("05-26-2022.xlsx").Activate
ActiveWindow.Close
]]

I keep getting an error.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I suggest you use Excel objects rather than Windows to do this. Don't depend on what is Active, be explicit. Also you must specify the worksheets you want to copy from and to, change as noted.

Rich (BB code):
   Dim myFileName As String
   Dim myFile As Workbook
   Dim YourFolderPath As Variant
   Dim rng As Range
   Dim B46 As Variant
   Set rng = Range(ActiveCell, ActiveCell)
  
   Sheets("2022 Oil Production").Select
   YourFolderPath = "X:\UTCS\Region\USA\Assets\GOM_DEV\Julia\SubSrfc\Rsvr_Mgmt-Surv\Surv-Data\Prod\St. Malo Morning Report - by Prod Date\2022"
   ChDir YourFolderPath
   ChDrive "X"
   myFileName = Application.GetOpenFilename
   Set myFile = Workbooks.Open(Filename:=myFileName)
   MsgBox "open" & myFile
   myFile.Worksheets("Worksheet Name Here").Range("B46").Copy
   Workbooks("Project 3. Oil Production copy (003).xlsx").Worksheets("Worksheet Name Here").Paste
   Windows("05-26-2022.xlsx").Activate
   myFile.Close
 
Upvote 0
I suggest you use Excel objects rather than Windows to do this. Don't depend on what is Active, be explicit. Also you must specify the worksheets you want to copy from and to, change as noted.

Rich (BB code):
   Dim myFileName As String
   Dim myFile As Workbook
   Dim YourFolderPath As Variant
   Dim rng As Range
   Dim B46 As Variant
   Set rng = Range(ActiveCell, ActiveCell)
 
   Sheets("2022 Oil Production").Select
   YourFolderPath = "X:\UTCS\Region\USA\Assets\GOM_DEV\Julia\SubSrfc\Rsvr_Mgmt-Surv\Surv-Data\Prod\St. Malo Morning Report - by Prod Date\2022"
   ChDir YourFolderPath
   ChDrive "X"
   myFileName = Application.GetOpenFilename
   Set myFile = Workbooks.Open(Filename:=myFileName)
   MsgBox "open" & myFile
   myFile.Worksheets("Worksheet Name Here").Range("B46").Copy
   Workbooks("Project 3. Oil Production copy (003).xlsx").Worksheets("Worksheet Name Here").Paste
   Windows("05-26-2022.xlsx").Activate
   myFile.Close

Where it says "Worksheet Name Here" does it automatically put the worksheet name or do I have to type it in everytime?
 
Upvote 0
Excel doesn't know what worksheet you want to copy from within the workbook. Don't you already know the worksheet name?

If it's always the first tab, for example, you can put a 1 in there instead of a name.
 
Upvote 0
I have a macro using the application.getopenfilename and it works great at selecting the file. But, I need to actually open the file copy from it and close it.

[
Dim myFile As String
Dim YourFolderPath As Variant
Dim rng As Range
Dim B46 As Variant
Set rng = Range(ActiveCell, ActiveCell)

Sheets("2022 Oil Production").Select
YourFolderPath = "X:\UTCS\Region\USA\Assets\GOM_DEV\Julia\SubSrfc\Rsvr_Mgmt-Surv\Surv-Data\Prod\St. Malo Morning Report - by Prod Date\2022"
ChDir YourFolderPath
ChDrive "X"
myFile = Application.GetOpenFilename
MsgBox "open" & myFile
Range("B46").Select
Selection.Copy
Windows("Project 3. Oil Production copy (003).xlsx").Activate
ActiveSheet.Paste
Windows("05-26-2022.xlsx").Activate
ActiveWindow.Close
]]

I keep getting an error.
After you get the filename, you have to open the file
VBA Code:
myfile = Application.GetOpenFileName
' validate myfile first
Workbooks.Open myfile
 
Upvote 0
Solution
Thank you Jon. I think the others would have worked as well. However, the sheet names that I open are different sheets every time. So I couldn't put the sheet file name in the macro. This get's me to my objective and I am so thankful for all your comments.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top