Code adjustment needed to push data directly to database with userform (VBA)

[kelly mort]

sample workbook at:

Sample_upload_to_database_file.xlsm

Shared with Dropbox

www.dropbox.com

This code was produced by @dmt32 somewhere in 2017. At the time, what I wanted to do was to simply get data from a range (on the local file) pushed to the database file and his code did the job greatly.

Now, what I want to achieve is:
1. Instead of pushing the data from the local workbook, I want to use a userform instead.
2. On the userform, once I click the command button, the data from the textboxes should be pushed directly to the database file

My ideas are limited at the moment and I want someone to help me fix it.

Thanks in advance.

 

[kelly mort]

This is how I got this working - I am ready to welcome better approaches though (If any). Thanks

Private Sub CommandButton1_Click()
    Dim DatabasePassword$, TemplateSheet$
    Dim wbDatabase As Workbook, wbTemplate As Workbook
    Dim DatabaseRange As Range, i&, j&
    Dim DatabaseName, msg, Data()
    
    DatabasePassword = ""
    '**********************************************************************************************
    'Database Path / Name
    DatabaseName = Cells(19, 9).Value

    If Len(DatabaseName) = 0 Then
        DatabaseName = BrowseFile
        If DatabaseName = False Then Exit Sub
        Cells(19, 9).Value = DatabaseName
    End If
    
    On Error GoTo myerror
    If Not Dir(DatabaseName, vbDirectory) = vbNullString Then
        Application.ScreenUpdating = False
        'Set wbTemplate = ThisWorkbook
        For j = 1 To 9
            i = i + 1
            ReDim Preserve Data(1 To i)
            With Controls("TextBox" & j)
                If Len(.Text) = 10 And IsDate(.Text) Then
                    Data(i) = DateValue(.Text)
                Else
                    Data(i) = .Value
                End If
            End With
        Next j

        Set wbDatabase = Workbooks.Open(DatabaseName, ReadOnly:=False, Password:=DatabasePassword)
        With wbDatabase.Sheets(1)
            Set DatabaseRange = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
        End With
        
        DatabaseRange.Resize(1, 9).Value = Data
      
        wbDatabase.Close True
        msg = Array("Template Data Saved", "Data Saved")
    Else
        msg = Array(DatabaseName & Chr(10) & "File Not Found", "Error")
    End If
    
myerror:
    Application.ScreenUpdating = True
    
    If Err > 0 Then
        If Not wbDatabase Is Nothing Then wbDatabase.Close False
        MsgBox (Error(Err)), 48, "Error"
    Else
        MsgBox msg(0), 48, msg(1)
    End If
    
    Set wbDatabase = Nothing
    Set wbTemplate = Nothing
    
    Set DataEntryRange = Nothing
End Sub