export data to special excel sheets

[benjamin.grimm]

hello VBA pro´s

in Access i can create different contracts with different running time.

I have conctracts with a running time of 4 years, 6years, 8 years, 10 years and 12 years.

I have for each contract a different Excel file.


The users can choose via an Inputbox, which contract he wants to Export in Excel. In the Inputbox he enters the SuWID.

Now i want that the the Excel file with the Special running time get opened.

4 years ----> ("C:\Users\GRIMBEN\Desktop\4years.xlsm")
6 years ----> ("C:\Users\GRIMBEN\Desktop\6years.xlsm")

and so on.

My code right now

    Dim xlApp As Object         'Excel.Application
    Dim xlBook As Object        'Excel.Workbook
    Dim xlSheet As Object       'Excel.Worksheet
    Dim rst As DAO.Recordset, SuWID As Long, tmpStr As String

    SuWID = InputBox("Welche ID möchten sie exportieren?", "Export value required", 1)
    
    Set rst = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SuWID, Jahr_Y, BT_Name, SAP_Nummer, Vertragsbeginn, Vertragsende FROM Abfrage_alles WHERE SuWID = " & SuWID)
    
    If rst.RecordCount > 0 Then
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        Set xlBook = xlApp.Workbooks.Open("C:\Users\GRIMBEN\Desktop\Try5.xlsm")
        Set xlSheet = xlBook.Sheets("Tabelle1")
        xlSheet.Range("A6:I18").ClearContents
        
        xlSheet.Range("A6").CopyFromRecordset rst
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    
    rst.Close
    Set rst = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

How can i do that?

greetz benjamin

 

[DavidAtWork]

try this:

Set xlBook = xlApp.Workbooks.Open("C:\Users\GRIMBEN\Desktop\" & SuWID & "years.xlsm")

This assumes SuWID is a number

David