Hello! I have to export two related tables (EmployeeData (ID,Name,Age) and PayingDate(ID,Income,Date) from Access to Excel.

The excel table should look like this:
1 John 35 ----->dates from EmployeeDate
  1400 15.05.2004 ----->dates from PayingDate
  1600 15.06.2004 ----->dates from PayingDate
  1145 15.08.2004 ----->dates from PayingDate
2 Mike 46
  2200 14.08.2004
  2100 15.09.2004

......... etc.



It's obvious i can't use TransferSpreadsheet in this case, so please help me with other ideas.

Hiya Crisan,

Try creating a crosstab query. Once you get the data back the way you want, you can then export it to excel.

Kevin

dartcoach

kevin, i tried to make the case as simplest as possible. In fact, I have to do with more complex tables, with many fields and a lot of grouping levels. I need the VBA code for this simple case just to understand the basic concepts of exporting to excel. After that, i will try to adapt the code on my real case. Thanks.

Crisan,

Here's some sample crosstab query sql:

TRANSFORM Sum(EmployeeData.Age) AS SumOfAge
SELECT EmployeeData.Name, EmployeeData.Age, PayingData.Id, PayingData.Income, PayingData.Date
FROM EmployeeData LEFT JOIN PayingData ON EmployeeData.Id = PayingData.Id
GROUP BY EmployeeData.Name, EmployeeData.Age, PayingData.Id, PayingData.Income, PayingData.Date
PIVOT EmployeeData.Id;

Once you've created the query, you can use it in the transferspreadsheet command just like a table.

Kevin

dartcoach

Kevin, first of all, thank you for efforts to help me.
Ok, with this crosstab I have all the information I need. This is good. The major problem is that I need to export this information into a simple and easy to understand excel file.
In the excel file , I need a main line with user personal data, followed by each line with Income and Date value for this user. Between users must be two blank lines.
I don't know if I could do this with TransferSpreadsheet. Please let me know if is possible.

Crisan,

I hope you can understand this. It will do what you want.

   Dim rs As Recordset
   Dim db As Database
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Query2") 'Name of your Crosstab query
   With rs
      .MoveFirst
      myID = !ID
      mycount = 1
      Do While Not .EOF
         If mycount = 1 Then
            mysql = "Select " & """" & Str(!ID) & """" & " as ID, " _
                  & """" & !Name & """" & " as Name, " _
                  & """" & Str(!Age) & """" & " as Age into Paydata;"
            DoCmd.RunSQL mysql
            mycount = mycount + 1
            mysql = "Insert into Paydata (ID, Name, Age) " _
                  & "Select " & """" & "" & """" & " as ID, " _
                  & """" & Str(!Income) & """" & " as Name, " _
                  & """" & Str(!Date) & """" & " as Age;"
            DoCmd.RunSQL mysql
         Else
            If myID = !ID Then
               mysql = "Insert into Paydata (ID, Name, Age) " _
                     & "Select " & """" & "" & """" & " as ID, " _
                     & """" & Str(!Income) & """" & " as Name, " _
                     & """" & Str(!Date) & """" & " as Age;"
               DoCmd.RunSQL mysql
            Else
               For I = 1 To 2
                   mysql = "Insert into Paydata (ID, Name, Age) " _
                         & "Select " & """" & "" & """" & " as ID, " _
                         & """" & "" & """" & " as Name, " _
                         & """" & "" & """" & " as Age;"
                   DoCmd.RunSQL mysql
               Next I
               myID = !ID
               mysql = "Insert into Paydata (ID, Name, Age) " _
                     & "Select " & """" & Str(!ID) & """" & " as ID, " _
                     & """" & !Name & """" & " as Name, " _
                     & """" & Str(!Age) & """" & " as Age;"
               DoCmd.RunSQL mysql
               mysql = "Insert into Paydata (ID, Name, Age) " _
                     & "Select " & """" & "" & """" & " as ID, " _
                     & """" & Str(!Income) & """" & " as Name, " _
                     & """" & Str(!Date) & """" & " as Age;"
               DoCmd.RunSQL mysql
            End If
        End If
        .MoveNext
    Loop

   End With
   Set rs = Nothing

   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PayData", "C:\PayData.xls", True

Hope this helps!

Kevin


dartcoach

Crisan,

I forgot, when you open the Excel file, since I had to convert numbers to text to insure that you could have both numeric and text and null all in the same column, you will have to convert the cells that are text but have numbers in them back to number format.

Kevin

dartcoach

 Thanks, Kevin! It looks like a good starting point. I will try to adapt this to my needs.
 btw, which is the safest way to prevent sorting of the PayData table? I want to avoid the accidentally scrambling of data from this table, because a lot of users will have rights for it.

Crisan,

Since the PayData table is created each time you execute this process, you can delete it after the export if you want. Since you have the spreadsheet you no longer need the table.

By the way, where are you located in the world?

Kevin

dartcoach

Kevin, I'm from Romania.