UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Aces Vba Export 2 Tables To1 Excel File With Several Sheets, Access 2016    
 
   
dekker_123
post Dec 4 2019, 04:24 AM
Post#1



Posts: 3
Joined: 4-December 19



I've been testing and searching for a couple of weeks and i think its now time after hundreds of tests, to ask help. Here's my situation.

I have two tables in MS Access:

CODE
Journal_personeel_verloning (or table1)

Table 1:

ID Persid Chauffeur Proj Starttime Endtime
1  85     John      A    10:00     12:00
2  86     Fred      X    10:00     12:00
3  85     John      A    10:00     12:00
4  86     Fred      A    10:00     12:00

Journal_personeel_verloning_2_all (or table2)

Table 2:
ID Persid Chauffeur Proj Totalworkedtime(decimal)
1  85     John      A    4
2  86     Fred      A    2
3  86     Fred      X    2


Notes:

Table 1 and table 2 have exactly the same employees always.

Persid is the fieldname should filter on (both tables has this fieldname)

What do I want to achieve: I want to export two tables to one Excel file with following conditions:

1 Excel file containing data of 2 tables
Each PersID on seperate sheet/tab
Each sheet/tab name would be PersID

Export must look like this:
CODE
1 excel file with:
tabname: John

ID Persid Chauffeur Proj Starttime Endtime
1  85     John      A    10:00     12:00
3  85     John      A    10:00     12:00

(1 or 2 empty rows)

ID Persid Chauffeur Proj Totalworkedtime(decimal)
1  85     John      A    4

same excel with second tabname: Fred

ID Persid Chauffeur Proj Starttime Endtime
2  86     Fred      X    10:00     12:00
4  86     Fred      A    10:00     12:00

(1 or 2 empty rows)

ID Persid Chauffeur Proj Totalworkedtime(decimal)
2  86     Fred      A    2
3  86     Fred      X    2


the code for only first table worked but i could not arrange both tables in one code with loop.

here is my code:
CODE
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
-----------------

Const strFileName As String = "Employee_Verloning"

DoCmd.SetWarnings False


Const strQName As String = "zExportQuery"

Set dbs = CurrentDb
strTemp = dbs.TableDefs(0).Name

strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"

Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close

strTemp = strQName

strSQL = "SELECT DISTINCT Persid FROM Journal_personeel_verloning;"

Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)


If rstMgr.EOF = False And rstMgr.BOF = False Then
      rstMgr.MoveFirst
      Do While rstMgr.EOF = False

            strMgr = DLookup("CHAUFFEURR", "Journal_personeel_verloning", _
                  "Persid = " & rstMgr!Persid.value)
            strSQL = "SELECT * FROM Journal_personeel_verloning WHERE " & _
                  "Persid = " & rstMgr!Persid.value & ";"


            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing


            DoCmd.TransferSpreadsheet _
            Transfertype:=acExport, _
            SpreadsheetType:=acSpreadsheetTypeExcel9, _
            TableName:=strTemp, _
            Filename:="M:\Public\Exports XLS\Personeel\" & strFileName & ".xls", _
            HasFieldNames:=True, _
            Range:=strMgr

            rstMgr.MoveNext


      Loop


End If


rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing


any help would be appreciate...thanks in advance...
This post has been edited by dekker_123: Dec 4 2019, 04:51 AM
Go to the top of the page
 
ADezii
post Dec 5 2019, 05:31 PM
Post#2



Posts: 2,708
Joined: 4-February 07
From: USA, Florida, Delray Beach


Unless I am mistaken, this may be a little more difficult than you imagine and cannot produce the desired results using TransferSpreadsheet. If you are interested, I can arrive at a solution that will be able to consolidate all the Data into a Results Table in the requested Format. This Results Table should then be easily exported to Excel. Just let me know, and I'll attach a simple Demo.

P.S. - My solution does involve the use of Nested Recordsets.
This post has been edited by ADezii: Dec 5 2019, 05:33 PM
Go to the top of the page
 
WildBird
post Dec 5 2019, 06:12 PM
Post#3


UtterAccess VIP
Posts: 3,676
Joined: 19-August 03
From: Auckland, Little Australia


TransferSpreadsheet wont work in this case, you will need to automate it. The fact you want to have data below other data complicates it a little bit, but doable. Just need to get the recordcount of the recordset.

Also, table 2 looks like it is just calculated from table 1? This shouldnt be stored, but rather can be done dynamically with a query.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
dekker_123
post Today, 03:09 AM
Post#4



Posts: 3
Joined: 4-December 19



Thanks for reply. I simple demo would be great smile.gif
Go to the top of the page
 
ADezii
post Today, 07:59 AM
Post#5



Posts: 2,708
Joined: 4-February 07
From: USA, Florida, Delray Beach


The following Demo will convert the Results into a Table which should then be easily exported to Excel either by the Table itself or a Query.
Attached File(s)
Attached File  Export_2_Tables.zip ( 31.94K )Number of downloads: 1
 
Go to the top of the page
 
dekker_123
post Today, 08:54 AM
Post#6



Posts: 3
Joined: 4-December 19



Dear ADezii,

thanks for your sample. Likes nice. smile.gif

Before youre example i tried to make a union all query. I arranged that. Then i was able to export that query to excel per sheet. But the output to excel isnt so nice.

that s the reason of my question:
If this output is possible? with both columnnames?
CODE
ID Persid Chauffeur Proj Starttime Endtime
1  85     John      A    10:00     12:00
3  85     John      A    10:00     12:00

(1 or 2 empty rows)

ID Persid Chauffeur Proj Totalworkedtime(decimal)
1  85     John      A    4


Let me ask other way:
is this output possible:
CODE
ID Persid Chauffeur Proj Starttime Endtime
1   85       John        A    10:00      12:00
3   85       John        A    10:00      12:00

(empty row)

ID Persid  A             B             C             D
1   85       Yellow      Metal      Good        Now
2   85       Green      PLastic    Bad          Tomorrow

So 2 different tables, only has same employees must on same emp sheet.
This post has been edited by dekker_123: Today, 08:55 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 09:06 AM