Populating Forms in Excel using VBA

Question

So i have a form that creates a chart based on a different file. I'm trying to write code that does this: User clicks "Get Data"

Form opens

User clicks the directory from which to get files

Code opens directory and populates a combobox in form with file names beginning with gmn

User clicks the files they want to get data from

macro is called and all data is added

I'm not any good with vba or excel but hey I'm learning. So the problem is that I have idea what to use to get the files? I tried looking at other post but they are just getting specific files. Any help is greatly appreciated! And if more info is needed let me know!

Code and Pics:

Private Sub ComboBoxDir_Change()

'populate our directories
Dim DirNow As String
DirNow = Dir("\\na.luk.com\wooster\DATA\NL-LUS-E\EAD\Tom_Freshly\SAE2\TestsDone\GMN10055\Ford-A oil\Inspection\" + Me.ComboBoxDir + "\*", vbNormal)

'loop to fill up pull down
Do While DirNow <> ""

    'add items to combo
    UserFormDataa.ComboBoxFiles.AddItem (DirNow)

    'get next dir
    DirNow = Dir()

Loop

'Now add files
Call GetFilesFromDirect("")



End Sub
Private Sub GetFilesFromDirect(DirectNow As String)

Dim file As Variant
   file = Dir("DirectNow")
   While (file <> "")
      If InStr(file, "Gmn*") > 0 Then
         ComboBoxFiles.AddItem (file)
      End If
     file = Dir
  Wend

End Sub

Private Sub ComboBoxFiles_Change()

End Sub

Private Sub GetSheets_Click()

   Call GetData

End Sub

If I'm guessing right, you need to retrieve the file items in the Do While loop right? — Sgdva
– Sgdva, Commented Jun 27, 2016 at 20:08
Yes @Sgdva I sort of have something going. I can get the directories into my directories combo box. Now i'm trying to code so that when user clicks on a directory, all the files in that directory are added to (Files) — MattCucco
– MattCucco, Commented Jun 27, 2016 at 20:12
If this is a coding exercise, fine. Otherwise, you're reinventing the wheel. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 27, 2016 at 20:13
I'm just trying to get the file names that start with gmn from a directory that the user clicks. Then add those files to a combobox. I see how this would work but I need it to only display the files in that directory — MattCucco
– MattCucco, Commented Jun 27, 2016 at 20:27

Sgdva · Accepted Answer · 2016-06-27 21:01:20Z

1

This should populate with all the elements that begin with "gmn" within DirNow Address

...
Do While DirNow <> ""
Dim oFSO as Object
Dim FileItem  as Variant
Dim oFolder as Object
Set oFSO = CreateObject("Scripting.FileSystemObject") 'library needed to do stuff related to file management
Set oFolder = oFSO.GetFolder(DirNow)
UserFormDataa.ComboBoxFiles.Clear 'no previous data needed (if any)
     For each FileItem in oFolder.Files
    'add items to combo
    If Instr(FileItem.Name,"gmn") >0 Then UserFormDataa.ComboBoxFiles.AddItem (FileItem.Name)
    'I'd ignore case "GMN" won't be added) If Instr(lCase(FileItem.Name)...
    Next FileItem 
Loop
...

OT: While I'm partially agree that File Dialog may be a better approach, I guess this kind of works too to make it easier to the users. I don't understand why comments suggested GetSaveAsFileName since you state there may be multiple user selections I'd go for GetOpenFilename or, it could be better to go with GetFolder

edited Jun 27, 2016 at 21:01

answered Jun 27, 2016 at 20:36

Sgdva

2,8103 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MattCucco Over a year ago

So it gave me an error :(. But what i did was i wrote the code for the user to click on an item in the Directory ComboBox. That item is stored and passed to a sub called GetFilesFromDirect(DirectNow as String) I took your code, put it in this sub, and changed every DirNow to DirectNow. Should I not do this?

Sgdva Over a year ago

That's the problem "DirNow" should be the path to the files

MattCucco Over a year ago

Got it.Just had to add the file path to DirNow. Thanks!

Collectives™ on Stack Overflow

Populating Forms in Excel using VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related