I created a 2 Queries. and exporting it to excel. However, they are on separate excel docs.
How can i merge the two excel together in access? (I cant merge the queries together because...access says the query is too complex)
is there a macro i can call to merge the two queries? and then exporting?
Last edited by lienlee; 06-10-2010 at 12:19 PM.
Do you want the queries on the same sheet or seperate sheets?
Same sheet. is there a macro to do that?Originally Posted by OBP
It is possible with VBA code, see this Thread
http://www.vbaexpress.com/forum/showthread.php?t=32475
It only pastes one query, but there is no reason why it can post a second query to the same sheet at a lower address.
Which code should i be looking at?
Thanks.
The code that I posted should do it, you just need to repeat this
Set rst = CurrentDb.OpenRecordset("TAD Personnel Query")
and this
objSheet.Range("A2").CopyFromRecordset rst
but change the cell location for ther second version
Sorry im new to VBA, but whats the code specifies which query is which.
The
Set rst = CurrentDb.OpenRecordset("TAD Personnel Query")
opens the query called "TAD Personnel Query" and puts the data in the recordset called rst.
So would it look like this then?.
Also
For Set objbook =objApp.Workbokos.Open
Does this have to be an existing spreadsheet?
Thanks.
[VBA]Set objApp = New Excel.Application
'This is new 'Your excel spreadsheet file goes here
Set objBook = objApp.Workbooks.Open("")
'Name of sheet you want to export to
Set objSheet = objBook.Worksheets(1)
objBook.Windows(1).Visible = True
'Opens the Query recordset
Set rst = CurrentDb.OpenRecordset("Query1")
Set dst = CurrentDb.OpenRecordSet("Query2")
objSheet.Range("A2:R65000").Select
'Clears the current contents in the workbook range
With objApp.Application.Selection
.ClearContents
End With
'rst Copies the recordset into the worksheet
objSheet.Range("A2").CopyFromRecordset rst
objSheet.range("A4").CopyFromRecordset dst
objBook.Save
objBook.Close
rst.Close
dst.Close
objApp.Visible = False
Set rst = Nothing
Set dst = Nothing
Set objSheet = Nothing
Set objBook = Nothing
Set objApp = Nothing
MsgBox "Excel has been Updated"
[/VBA]
I think if it doesn't exist, it will be created.
Hey, soo i tried without an existing file and it didnt work. but it works with one.
When i use the code; it works perfect, however, the excel sheet that it outputs doesnt look like the datasheet view i have.
For my queries. i used advance filter and sort to organize my data to the way i want it.
how can i fix this?
Can you show a screenshot of how they both look?
This isnt in alpha order. It seems that my code in advance filter & sort didnt apply.
MS Access
http://i.imagehost.org/download/0375/msaccess
Excel
http://yfrog.com/f/jcexcelkp/
If you Link to the Query in Excel does it come out in the correct order?
Are you setting the Query's Sort to get the data how you want it?
If i use the macro to output to excel. it comes out in the correct order. however, its 2 separate files.
In the advance filter & sort.
Under Field i zoomed and use the following code to get my order. (this is part of it)
Without the filter & sort; the default is alphabetical.
[vba]IIf([Subject_Area_Name]="Inventory",1,IIf([Subject_Area_Name]="Warehouse Logistics",2,IIf([Subject_Area_Name]="Transportation",3,IIf([Subject_Area_Name]="Purchase Orders",4,
IIf([Subject_Area_Name]="Shipments",5,
IIf([Subject_Area_Name]="Store Order",6,IIf([Subject_Area_Name]="Lead Time",7,
IIf([Subject_Area_Name]="Retail Order",8,IIf([Subject_Area_Name]="Sales Support",9,IIf([Subject_Area_Name]="Retail Sales",10,IIf([Subject_Area_Name]="Product",11,
IIf([Subject_Area_Name]="Services",12,IIf([Subject_Area_Name]="Part",13,
IIf([Subject_Area_Name]="Costing",14,15))))))))))))))[/vba]
Can't you set up the correct sorting in the Access Query?
access cant handle the huge query. theres a limit.
i have 64 rows..lol
edit: i created my custom order in excel..
for the code you gave me. how would i go about to creating a safety measure if
Set objBook = objApp.Workbooks.Open("") is incorrect
Last edited by lienlee; 06-15-2010 at 01:18 PM.
An Access VBA SQL Recordset can handle the 64 rows, but it is quite a bit of programming.
You need a table in Access consisting of [Subject_Area_Name] and the sort order, and include the sort order in your query (joined by[Subject_Area_Name]). Then you just sort the query by the sort order number.
Posting Permissions
Reply With Quote
