PDA

View Full Version : Solved: Merging Queries/Excel



lienlee
06-10-2010, 10:45 AM
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?

OBP
06-11-2010, 03:43 AM
Do you want the queries on the same sheet or seperate sheets?

lienlee
06-11-2010, 04:24 AM
Do you want the queries on the same sheet or seperate sheets?

Same sheet. is there a macro to do that?

OBP
06-11-2010, 04:45 AM
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.

lienlee
06-11-2010, 06:18 AM
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.

OBP
06-11-2010, 07:29 AM
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

lienlee
06-14-2010, 07:36 AM
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. :banghead:

OBP
06-14-2010, 08:17 AM
The
Set rst = CurrentDb.OpenRecordset("TAD Personnel Query")
opens the query called "TAD Personnel Query" and puts the data in the recordset called rst.

lienlee
06-15-2010, 07:41 AM
So would it look like this then?.
Also
For Set objbook =objApp.Workbokos.Open
Does this have to be an existing spreadsheet?
Thanks.



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"

OBP
06-15-2010, 09:04 AM
I think if it doesn't exist, it will be created.

lienlee
06-15-2010, 09:41 AM
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?

OBP
06-15-2010, 10:33 AM
Can you show a screenshot of how they both look?

lienlee
06-15-2010, 10:44 AM
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/

OBP
06-15-2010, 11:07 AM
If you Link to the Query in Excel does it come out in the correct order?

OBP
06-15-2010, 11:11 AM
Are you setting the Query's Sort to get the data how you want it?

lienlee
06-15-2010, 11:13 AM
If you Link to the Query in Excel does it come out in the correct order?
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.

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))))))))))))))

OBP
06-15-2010, 11:19 AM
Can't you set up the correct sorting in the Access Query?

lienlee
06-15-2010, 11:35 AM
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

OBP
06-16-2010, 03:40 AM
An Access VBA SQL Recordset can handle the 64 rows, but it is quite a bit of programming.

geekgirlau
06-21-2010, 09:35 PM
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.

OBP
06-22-2010, 08:11 AM
geekgirlau, that is a neat idea. :thumb