kay soo
asked on
Append data of two different Access Tables into a same Excel Workbook/Worksheet
Hi, I am using the following VBA to generate Excel Book1.xls and posting Access Table1 into sheet1. After Table1 posting and while the Book1.xls is opened, I hv a hidden form running a For Next loop to generate data for Table2. I like to post second Access Table2 into the same Book1.xls Sheet1 after the For Next loop, how do I do that?
Attached Book1.xls is the output result intended.
Book1.xls
Private Sub Command1_Click()
Dim CountRecord As Integer
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim dbs As DAO.Database
Dim nLastRow As Long
DoCmd.SetWarnings (WarningsOff)
Set dbs = CurrentDb
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(strSheetName) > 0 Then
xlWSh.Name = Left(strSheetName, 34)
End If
Set rst = dbs.OpenRecordset("Table1", dbOpenDynaset)
xlWSh.Activate
rst.MoveFirst
xlWSh.Range("A1").Select ' Header Posting
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
xlWSh.Range("A2").CopyFromRecordset rst ' Data Posting
rst.Close
Set rst = Nothing
' Hidden form running a For Next Loop data calculating..
'Activate Book1 Sheet1 follow by Table2 posting........
'e.g.
.
.
.
xlWSh.Activate
rst.MoveFirst
xlWSh.Range("A8").Select ' Header Posting
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
Set rst = dbs.OpenRecordset("Table2", dbOpenDynaset)
xlWSh.Range("A9").CopyFromRecordset rst ' Data Posting
rst.Close
Set rst = Nothing
end subAttached Book1.xls is the output result intended.
Book1.xls
ok, I think I see it. Your headings are referencing the same cell for each of your recordset field names. You need to change the offset numbers to move the target cell to the next cell:
For X=0 to rst.Fields.Count-1
xlWSh.Range("A8").Offset(0
Next X
For X=0 to rst.Fields.Count-1
xlWSh.Range("A8").Offset(0
Next X
ASKER
The code works fine actually, but I need help on modification how to re-focus on Book1 to post Table2 instead of the code will open up Book2 for Table2.
The reasons is that the posting is done from a hidden form running at the background with For Next Loop algorithm, the first loop is to post Table1 with Book1.xls (by default), then run a second loop to post Table2, while doing that, Book1.xls remains opened but minimized by Windows OS, so I need help of the coding to reactivate Book1 and refocus on Sheet1 to append Table2.
The reasons is that the posting is done from a hidden form running at the background with For Next Loop algorithm, the first loop is to post Table1 with Book1.xls (by default), then run a second loop to post Table2, while doing that, Book1.xls remains opened but minimized by Windows OS, so I need help of the coding to reactivate Book1 and refocus on Sheet1 to append Table2.
ASKER
oh btw, the code is running at on current event from a hidden continuous form instead of button click.
1st on current event is to post Table1
2nd on current event is to post Table2
1st on current event is to post Table1
2nd on current event is to post Table2
Your additional information helps.
Try setting the .Visible property of your Excel application object to False and activating your workbook before running the second set.
Excel workbooks automatically pop-out of minimize when you try to work with them with VBA. I've never been able to manipulate a workbook while it was minimized. Making the Excel application object invisible, however, allows you to work on it without it being seen by the user.
Try setting the .Visible property of your Excel application object to False and activating your workbook before running the second set.
Excel workbooks automatically pop-out of minimize when you try to work with them with VBA. I've never been able to manipulate a workbook while it was minimized. Making the Excel application object invisible, however, allows you to work on it without it being seen by the user.
Another trick to avoid problems is to activate your worksheet before messing with it.
xlWSh.Activate
It's because of an Excel thing. For some reason it will sometimes throw an error if you don't do that. Don't ask me why, but activating the worksheet before messing with it prevents issues.
xlWSh.Activate
It's because of an Excel thing. For some reason it will sometimes throw an error if you don't do that. Don't ask me why, but activating the worksheet before messing with it prevents issues.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi PatHartman,
Union query won't be suitable for this case, because I hv to post a sum up query at the end of each Table posting.
Furthermore, company requested to hv a row spacing between Table1 and Table2 in excel for ease of understanding from users.
I managed to get everything done, except after Table1 posting, my code open up another workbook for Table2.
I shall try out Mark's suggestion, is something new for me to learn.
Much appreciated if any other suggestions provided by Gurus.
Union query won't be suitable for this case, because I hv to post a sum up query at the end of each Table posting.
Furthermore, company requested to hv a row spacing between Table1 and Table2 in excel for ease of understanding from users.
I managed to get everything done, except after Table1 posting, my code open up another workbook for Table2.
I shall try out Mark's suggestion, is something new for me to learn.
Much appreciated if any other suggestions provided by Gurus.
ASKER
Tq for all the input, I took Pat's approach but with a slight alteration, instead of a Union Query, I created a temp Table and start dumping all the For Next Loop generated Table1 + Sum data into it by using Append Query, row spacing between tables is by append a "-" character into the next row before executing Table2 + Sum append, after End Sub then posting the temp table with all the data into Excel.
Is a bit rough but it sort of getting the thing done.
Tq for all the ideas and tips, thumbs up.
Is a bit rough but it sort of getting the thing done.
Tq for all the ideas and tips, thumbs up.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thx , this is a much better approach.
You're welcome.
When it comes to exporting toExcel, there are two methods available:
1. Using Access TransferSpreadsheet() function: You can target a named-range in an Excel workbook with this Access function. Just insert a single-cell named-range where you want to put the top-left corner of your data block and put the named-range as the Range parameter of the function. The function will even auto-resize (grow or shrink) the named-range to fit the exported data block! You can even pre-format your columns. The TransferSpreadsheet function won't overwrite your formatting, it just adds the data. Works like a charm.
2. Using Excel CopyFromRecordset() function: The advantage that the CopyFromRecordset (CFR) function has over Access's TransferSpreasheet function is that you can target a Range with a normal sheet name and range address like "F6" instead of a named-range. One of the major drawbacks, however, is that it doesn't include the headings - you have to add those separately. This is your chosen method. What's wrong with your code?