How to use GetOpenFileName

APML

New Member
Joined
Sep 10, 2021
Messages
31
Office Version
  1. 365
Hi All, just hoping someone can help. I pretty simple when it comes to vba, i generally look online a find the code and adjust to make it work. I'm trying to use GetOpenFile.
I'm hoping to open a box with all csv file from a specific folder but atm I'm unable to workout how to do that..(the specific folder part)
So far this is what I've done, it works, but hoping to get can help on how I adapt so it only opens a specific folder holding numerous csv files. The folder name is CSV_ALL

VBA Code:
Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.csv*),*csv*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A1:g785").Copy
        ThisWorkbook.Worksheets("Red").Range("A3").PasteSpecial xlPasteValues
        OpenBook.Close False
    End If
    Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,174
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What is the full path of the specific folder?
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,174
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perhaps this:

VBA Code:
Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim FolderPath As String
   
    FolderPath = "C:\CSV_ALL"
    VBA.ChDir FolderPath
   
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.csv*),*csv*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A1:g785").Copy
        ThisWorkbook.Worksheets("Red").Range("A3").PasteSpecial xlPasteValues
        OpenBook.Close False
    End If
    Application.ScreenUpdating = True
End Sub
 
Solution

APML

New Member
Joined
Sep 10, 2021
Messages
31
Office Version
  1. 365

ADVERTISEMENT

Hey WOW. Thanks, works great.
I did get a popup message that says:

"There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later?
to save it on the clipboard so that you can paste later, click Yes
to delete it from the clipboard and free memory click No

I don't need the info on the clipboard so I clicked No, just wonder if there is a setting to get rid of that message (I would never need to save the data to the clipboard)
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,131
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Try replacing:

VBA Code:
        OpenBook.Close False

with:

VBA Code:
    Application.DisplayAlerts = False
    OpenBook.Close False
    Application.DisplayAlerts = True
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You could also add this line, which should clear the clipboard.
VBA Code:
ThisWorkbook.Worksheets("Red").Range("A3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
OpenBook.Close False
 

APML

New Member
Joined
Sep 10, 2021
Messages
31
Office Version
  1. 365
You could also add this line, which should clear the clipboard.
VBA Code:
ThisWorkbook.Worksheets("Red").Range("A3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
OpenBook.Close False
Works great, thx for your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,570
Messages
5,770,915
Members
425,652
Latest member
Pemby

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
Top