User input FileDialog to path for source files in VBA

Question

SOLVED As it was pointed out by @z32a7ul I was using the wrong variable to state the path after the FileDialog. It was supposed to be OutPathS instead of OutPath.

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 following 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

        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)

**'MY ERROR IS HERE, It has to be OutpathS:
path = OutPath & "\*.*" 'this should be: path = OutPathS & "\*.*"**


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.

Can you try this: `varResult = Application.GetSaveAsFilename(FileFilter:="Comma Separated Values(*.csv), *.csv")` — Vityata, Feb 21 '17 at 15:55
Aren't you missing a \ from InitialFileName? Shouldn't it be "D:\StartingPath" instead of "D:StartingPath"? — z32a7ul, Feb 21 '17 at 15:57
You save the result of Mid to OutPathS but calculate path from OutPath (without S). — z32a7ul, Feb 21 '17 at 15:59
You cannot be sure that OutPath will contain the string "\StartingPath" because the user may navigate anywhere. Or if you make a verbal agreement, which folder they should choose, then you don't need a dialog. — z32a7ul, Feb 21 '17 at 16:02
@z32a7ul Hhaha, right on friend! I completely missed that. Should Have used OutPathS for the path. — DGMS89, Feb 21 '17 at 16:02
Your question reads rather awkwardly for a Stack Overflow post. Questions should read like questions, answers are found in answers. You took the [tour]? — Mathieu Guindon, Feb 21 '17 at 16:10
@Mat'sMug I sure did, and ideed you are right. But considering this was a very simple (albeit stupid) mistake, I just fixed it directly. The answer below also solves the problem, but a bigger chunk of the code has to be changed. That is why I accepted both. — DGMS89, Feb 21 '17 at 16:19

FunThomas · Accepted Answer · 2017-02-22T18:03:25.770

3

If you need only to select a folder, consider using Application.FileDialog(msoFileDialogFolderPicker)

A function returning a selected folder could look like

 Function GetFolder(initPath As String) As String
    Dim dialog As FileDialog

    Set dialog = Application.FileDialog(msoFileDialogFolderPicker)
    dialog.title = "Select a Folder"
    dialog.AllowMultiSelect = False
    dialog.InitialFileName = initPath 

    If dialog.show Then
        GetFolder = dialog.SelectedItems(1)
    Else
        GetFolder = ""
    End If
    Set dialog = Nothing
End Function

edited Feb 22 '17 at 18:03

answered Feb 21 '17 at 15:52

FunThomas

23,043
3
18
34

Thanks for the answer. This also works fine, but required some code modifications. Considering that the original problem was just a variable mistake, I will accept this one as the answer. – DGMS89 Feb 21 '17 at 16:05

User input FileDialog to path for source files in VBA

1 Answers1