Macro to select Specified Directory

[howard]

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

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

 

[AlphaFrog]

Put this at the very top of your Code Module

Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long

Run this line in your macro in place of ChDir

SetCurrentDirectoryA "\\tyr\reports\BR1"

 

[howard]

Thanks AlphaFrog

Kindly amend Code for 64 Bit Office

[/code]Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long [/code]

 

[AlphaFrog]

A web serarch returned this. I didn't test it as I'm still using a 32 bit version.

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

64 bit error

I'm having a problem getting this code to work on the new VBA7. Can someone please help me out? I've read the articles on how to fix it, but I'm lost. Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String)...

www.mrexcel.com

 

[howard]

Thanks for letting me know