CODEWITHSUNDEEP
vbaexcel
MattCucco
MattCucco

Reputation: 133

Populating Forms in Excel using VBA

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

enter image description here

Upvotes: 0

Views: 296

Answers (1)

Sgdva
Sgdva

Reputation: 2800

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

Upvotes: 1

Related Questions