Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

link multiple files contains form based on selected combobox

0

Hello

I would link FORM file with SDE file as in example(there are many files like SDE  file)   .

if I select sheet name from combobox1 in FORM file and match with sheet name in SDE file should populat data from SDE file to  FORM file into listbox as the original code does it.shouldn't SDE file or others are open  and files will be closed when import data from them .

all of files are existed in the same folder .

thanks

Answer
Discuss

Answers

0
Selected Answer

Kalil

In the attached revised file, I've added a new ComboBox2 (with associated textbox "File Name") to your UserForm.

I've declared a new variable (for your file folderpath) which is at the top of the UserForm code (and so "known" to all UserForm procedures):

Option Explicit
Dim va, vc
Dim oldValue As String
Dim FldPth As String

Then, when the form is launched, this modified code runs (changes in bold, with comments):

Private Sub UserForm_Initialize()
    Dim x
    For Each x In Array("PURCHASE", "SALES", "VV", "RETURNS")
        ComboBox1.AddItem x
    Next
    ListBox1.ColumnCount = 9
    ListBox1.ColumnWidths = "100,60,60,100,80,70,50,50,50"

    Dim NxtFl As String

    ' put your folder path here, ending in backslash \
    FldPth = "E:\Documents\Spreadsheets\TeachExcel files\Hasson files\"
    ' get first matching file
    NxtFl = Dir(FldPth & "*.xls*")
    ' loop
    While NxtFl <> ""
        ' add to CB2, if not this file
        If NxtFl <> ThisWorkbook.Name Then ComboBox2.AddItem NxtFl
        ' get next file (if any)
        NxtFl = Dir
    Wend

End Sub

where you MUST edit the filepath (just in bold below) before trying this with your files, taking note of the comment:

    ' put your folder path here, ending in backslash \

    FldPth = "E:\Documents\Spreadsheets\TeachExcel files\Hasson files\"

Now the new ComboBox will list files matching the "wildcard" search string in the line starting NxtFl =

If you pick a file, it runs your CB1 code (in case you  picked the SHEET first):

Private Sub ComboBox2_Change()
    ' see if a sheetname was already picked
    Call ComboBox1_Change

End Sub

When you pick a sheet (after picking a file), this modified code runs:

Private Sub ComboBox1_Change()

    Dim Wb1 As Workbook
    ' do nothing unless a file and sheetname is picked
    If ComboBox1.Value = "" Or ComboBox2.Value = "" Then Exit Sub
    Application.ScreenUpdating = False
    'Open the selected workbook
    Set Wb1 = Workbooks.Open(FldPth & ComboBox2.Value)

    Dim n As Long, i As Long

    With ComboBox1
    va = Empty
    If Evaluate("isref('" & .Value & "'!A1)") Then
       n = Sheets(.Value).Range("D" & Rows.Count).End(xlUp).Row
       va = Sheets(.Value).Range("A2:I" & n)
       If n > 1000 Then n = 1000
       vc = Sheets(.Value).Range("A2:I" & n)
       Call toFormat(va)
       Call toFormat(vc)
       ListBox1.List = vc
    End If
    End With
    TextBox1 = ""
    'Close the workbook after populating listbox
    Wb1.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

You'll need to change your code for date filters in a similar way but you should get the idea.

Hope this fixes your problem- if so, please remember to mark this Answer as Selected.

Discuss

Discussion

Wow !
select file is good idea !
thank you so much
Kalil (rep: 38) Jun 27, '24 at 3:35 am
Glad you liked it. Thanks for selecting my Answer, Kalil
John_Ru (rep: 6297) Jun 27, '24 at 5:18 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login