Export to excel

[benjamin.grimm]

Hello guys,

i have a database, which analyse rent contracts.

I export each rent contract to excel by the following code.

Each contract gets each spreadsheet. So right now i export all contracts.

Private Sub Befehl1_Click()
    Dim xlApp As Object         'Excel.Application
    Dim xlBook As Object        'Excel.Workbook
    Dim xlSheet As Object       'Excel.Worksheet
    Dim rstID As DAO.Recordset, tmpStr As String
    Dim rstGr As DAO.Recordset, strSQL As String
 
    strSQL = "SELECT SuWID FROM Abfrage_alles GROUP BY SuWID;"
    Set rstID = CurrentDb.OpenRecordset(strSQL)
    If rstID.RecordCount > 0 Then
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        Set xlBook = xlApp.Workbooks.Open("S:\Access\SuW\Excel-Tabellen\ID.xlsm")
        Do While Not rstID.EOF
 
           Set xlSheet = xlBook.Sheets("Tabelle " & rstID.Fields("SuWID"))
            xlSheet.Name = "ID" & rstID.Fields("SuWID")
 
 
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SuWID, Jahr_Y, BT_Name, SAP_Nummer FROM Abfrage_alles WHERE SuWID = " & rstID.Fields("SuWID"))
            xlSheet.Range("A4").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    rstID.Close
    Set rstID = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

Is it possible that, before the exports starts that an inport box shows up and the user can enter special contracts by SUWID number?

For example 5,6,7 and 10.

Greetz benjamin

 

[namliam]

Yes would be possibly by doing something like:
strSQL = "SELECT SuWID FROM Abfrage_alles where SUWID in (" & YourCommaSeperatedList& ") GROUP BY SuWID;"

 

[benjamin.grimm]

and where in the code do i have to put that?

greetz benjamin

 

[namliam]

Surely you ARE KIDDING right?

The hard part might be figuring out how to get the comma seperated value from the user

 

[benjamin.grimm]

How do you mean this with the comma?

I have not so much clue about VBA, this is the reason for my question

greetz benjamin

 

[namliam]

For example 5,6,7 and 10.

This with the comma, the in text needs to be exactly that 5,6,7,10 or it will fail.

I dont care if you have little experience with vba, you must have made or got this code someplace and have SOME clue as to how it works. If you do, then it shouldnt be to hard which line I took and addapted how in your code to give you this example....

Let me give you an extra hint, the red part is what is new.

strSQL = "SELECT SuWID FROM Abfrage_alles where SUWID in (" & YourCommaSeperatedList & ") GROUP BY SuWID;"

 

[benjamin.grimm]

ok thanks,

Sorry for the confusion, acutally i wanted to know what does that mean

What is that?

(" & YourCommaSeperatedList & ")

I want to learn thats the reason why i ask?

greetz benjamin

 

[pr2-eugin]

Hello Benjamin, long time no see !

Well what namliam is saying is that, you first need to obtain the list (5,6,7,8, 10) from the user probably using an InputBox, then use that in the code,

[B]:[/B]
Dim [COLOR=Red]YourCommaSeperatedList [COLOR=Black]As String
[/COLOR][/COLOR][COLOR=Red]YourCommaSeperatedList [COLOR=Black]= InputBox("Enter the list of SUWID, seperated by comma.")

[/COLOR][/COLOR]strSQL = "SELECT SuWID FROM Abfrage_alles [COLOR=Red]where SUWID in (" & YourCommaSeperatedList & ")[/COLOR] GROUP BY SuWID;" 		
[B]:[/B]

 

[benjamin.grimm]

hey eugin,

thats right.

great hint.

Can i also ask for the SAP number or do i have to change my querries then?

greetz benjamin

 

[pr2-eugin]

You might need to edit the Query if you want to include SAP numbers.

 

[namliam]

As long as the sap numbers are NUMBERS and are completely different from the SUWID....

strSQL = " SELECT SuWID " & _ 
         " FROM Abfrage_alles " & _ 
         " where SUWID in (" & YourCommaSeperatedList & ") " & _ 
         "    or SAPID in (" & YourCommaSeperatedList & ") " & _ 
         " GROUP BY SuWID;"

Offcourse you may want to change your inputbox as well

More "beautifull" is actually if you would allow the user to pick them from your database on a nice form, but that is probably a bridge to far ahead atm.

 

[benjamin.grimm]

Thanks a lot for your help guys,

i will try that and i will let you know.


greetz benjamin.