Macro to select Specified Directory

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,603
Office Version
  1. 2021
Platform
  1. Windows
I have code below to enable a user to select CSV files in a specified folder ,"\\tyr\reports\BR1", but when running the code the last folder is selected


Kindly amend my code

Code:
Sub Open_Workbook()
    
FormulaMin
Delete_RowsBelowMinDate
    
    Dim A As Variant
    Dim LR As Long
    
    ChDir "\\tyr\reports\BR1"
    
    ' Clear contents of "Data Import" sheet
    With ThisWorkbook.Sheets("Data Import")
        .UsedRange.ClearContents
    End With
    
    A = Application.GetOpenFilename(MultiSelect:=True)
    
    If TypeName(A) = "Boolean" Then Exit Sub
    
    Dim file As Variant
    
    Application.ScreenUpdating = False
    
    For Each file In A
        With Workbooks.Open(file, Local:=True)
            With Sheets(1)
                .Range("A1:S" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy _
                    Destination:=ThisWorkbook.Sheets("Data Import").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End With
            .Close SaveChanges:=False
        End With
    Next
    
    Application.ScreenUpdating = True
    
    Del_Unwanted
    RefreshAllPivots
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Put this at the very top of your Code Module
VBA Code:
Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long


Run this line in your macro in place of ChDir
VBA Code:
SetCurrentDirectoryA "\\tyr\reports\BR1"
 
Upvote 0
Thanks AlphaFrog

Kindly amend Code for 64 Bit Office

[/code]Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long [/code]
 
Upvote 0
A web serarch returned this. I didn't test it as I'm still using a 32 bit version.

VBA Code:
Private Declare PtrSafe Function SetCurrentDirectoryA Lib _
    "kernel32" (ByVal lpPathName As String) As LongPtr

 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,214
Members
453,024
Latest member
Wingit77

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