Command-button om gegevens van Access naar Excel te exporteren

Status
Niet open voor verdere reacties.
K

Kirana2014

Gebruiker
Lid geworden
21 okt 2020
Berichten
55
Hoe maak je van 3 Command button naar 1 command button (zie hieronder de VBA die ik gebruik)
Ik zou graag 1 Command button willen hebben in plaats van 3
Als gegevens geëxporteerd, verschijnt MsgBox ("CustomerorderJKT, CustomerorderDPS, CustomerorderBD is succesvol geëxporteerd")
Ik gebruik onderstaande VBA
Bij voorbaat dank


Code: 
Private Sub Export_to_excel_JKT_Click()
Dim filepath As String
Dim sSheetName As String
sSheetName = InputBox("Click OK to continue!", Default:="DataOrderJKT")
If sSheetName <> "" Then
filepath = "C:\Access\Database\Database2021.xlsb"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CustomerorderJKT", filepath, True, sSheetName
MsgBox ("CustomerorderJKT has been successfully exported")

End If
End Sub


Private Sub Export_to_excel_DPS_Click()
Dim filepath As String
Dim sSheetName As String
sSheetName = InputBox("Click OK to continue!", Default:="DataOrderDPS")
If sSheetName <> "" Then
filepath = "C:\Access\Database\Database2021.xlsb"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CustomerorderDPS", filepath, True, sSheetName
MsgBox ("CustomerorderDPS has been successfully exported")

End If
End Sub


Private Sub Export_to_excel_BD_Click()
Dim filepath As String
Dim sSheetName As String
sSheetName = InputBox("Click OK to continue!", Default:="DataOrderBD")
If sSheetName <> "" Then
filepath = "C:\Access\Database\Database2021.xlsb"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CustomerorderBD", filepath, True, sSheetName
MsgBox ("CustomerorderBD has been successfully exported")

End If
End Sub
 
Leg eens uit wat je wilt, want ik vind het nogal onlogisch allemaal. Drie codes die drie verschillende bestanden maken kun je best onder één knop hangen, maar wat is de logica achter de drie Inputboxen?
Maar het antwoord op jouw vraag ziet er dan ongeveer zo uit:
Code: 
Option Compare Database
Dim filepath As String
Dim sSheetName As String
Dim msg As String

Code: 
Private Sub Export_to_excel_Click()
    sSheetName = InputBox("Click OK to continue!", Default:="DataOrderJKT")
    filepath = "C:\Access\Database\Database2021.xlsb"
    If sSheetName <> "" Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CustomerorderJKT", filepath, True, sSheetName
        msg = "CustomerorderJKT has been successfully exported"
    End If
    sSheetName = InputBox("Click OK to continue!", Default:="DataOrderDPS")
    If sSheetName <> "" Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CustomerorderDPS", filepath, True, sSheetName
        tmp = tmp & vbLf & "CustomerorderDPS has been successfully exported"
    End If
    sSheetName = InputBox("Click OK to continue!", Default:="DataOrderBD")
    If sSheetName <> "" Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CustomerorderBD", filepath, True, sSheetName
        tmp = tmp & vbLf & "CustomerorderBD has been successfully exported"
    End If
    MsgBox tmp, vbOKOnly
End Sub
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan