User input FileDialog to path for source files in VBA

[dgms89]

Greetings,

**What code does:** I have a code that reads the files in a folder, prints the names in the active workbook, and then puts the names in ascending order.


**Obs1:** I have follow up codes that use this information for calculations, but this part is not relevant to the current problem.


**Objective:** I am trying to create a FileDialog so the user can input the folder in which are the source files.


**Problem:** I create a code for this, but for some reason, it is not reading the source files, even though the format is the same.


**Where I got so far:** If I remove this user input and just "hardcode" the address of the sources (assuming my gatherer workbook is in the same folder as them), everything works fine. But then I am limited to where I can place this "gatherer" workbook.


**Question:** I am getting no specific error line. The result is the problem, since it does not find the source files. Does anyone have any idea on what to do here?


**Code:**

Option Explicit


    Public path As String


    Sub Counter()


    Dim count As Integer, i As Long, var As Integer
    Dim ws As Worksheet
    Dim w As Workbook
    Dim Filename As String
    Dim FileTypeUserForm As UserForm
    Dim X As String
    Dim varResult As Variant
    Dim OutPath As String, OutPathS As String, wPos As Long


    Set w = ThisWorkbook


    Application.Calculation = xlCalculationManual
     
     'source input by user, THIS IS THE PROBLEMATIC PART****
    
            varResult = Application.GetSaveAsFilename(FileFilter:="Comma  Separated Values Files" & "(*.csv), *.csv", Title:="OutPath", InitialFileName:="D:StartingPath")
    
            If varResult <> False Then
                OutPath = varResult
                w.Worksheets("FILES").Cells(1, 4) = varResult
    
            Else
    
                Exit Sub
    
            End If


    wPos = InStr(OutPath, "\StartingPath")
    OutPathS = Mid(OutPath, 1, wPos - 1)


    path = OutPath & "\*.*"
    
    
    Filename = Dir(path)
    
    ThisWorkbook.Sheets("FILES").Range("A:A").ClearContents
        
    X = GetValue
    If X = "EndProcess" Then Exit Sub
    
    
    Set ws = ThisWorkbook.Sheets("FILES")
    i = 0
    Do While Filename <> ""
        var = InStr(Filename, X)
        
        If var <> 0 Then
            i = i + 1
            ws.Cells(i + 1, 1) = Filename
            Filename = Dir()
            
        Else: Filename = Dir()
        End If
        
    Loop
    
    Range("A2:A" & i).Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlNo     'this will sort the names directly in the "FILES" sheet
        
    Application.Calculation = xlCalculationAutomatic
    
    ws.Cells(1, 2) = i
    
    MsgBox i & " : files found in folder"
    End Sub




    Function GetValue()
    With FileTypeUserForm
        .Show
        GetValue = .Tag
    End With
    Unload FileTypeUserForm
    End Function

**Obs2:** There is a public variable because it is going to be used in a subsequent macro, for calculations.


**Obs3:** The whole filedialog part is just to find the path where the source files are. It does not save anything.

 

[mikerickson]

Have you tried Application.GetOpenFile name rather than SaveAsFileName?

 

[dgms89]

It turns out the problem was in the line

[COLOR=#333333]path = OutPath & "\*.*"[/COLOR]

I used the wrong variable, it should be:

[COLOR=#333333]path = OutPathS & "\*.*"[/COLOR]