Access export queries to excel, with date equal to date in row

[MushroomKing]

Hi everyone!

I've been trying to export queries to excel in vba. Which works alright but needs some work still.

I am completely stuck on where to start when it comes to exporting the data in dynamic (date) cells. Let me explain:

- I have multiple queries in Access. (Qry1, Qry2, Qry3)
- I have an Excel workbook with a sheet that needs to be filled out.
- I have a date column in Excel
- My exported queries are filling in the rows.

This works, but i want to export to the row with the same date that i specified in access!

So if my dates in Excel are in column A, and the date in the Access form field = equal to the date in Row 1 in Excel, i want to export to:

Qry1 > B1
Qry2 > C1
Qry3 > D1

If that makes sense :S. So far i only have this:

DoCmd.TransferSpreadsheet acExport, 8, "Qry1", "C:\accesstool...", True, "B1"

Can somebody please help me with this? I have no clue.
Also would like to keep it simple, im not an Access guru really.

 

[Minty]

I'm not getting what you are trying to achieve here?
Are you trying to match a cell in an existing worksheet to the query results?
This is likely to be very difficult as the only way to "read" a spreadsheet value would be to link to it, and once it's linked and opened by Access I don't think you can update the values in it.

Perhaps a couple of pictures would help, or some actual data.
Start with what you have in the query , and what you would like in the spreadsheet.

 

[MushroomKing]

Thanks for the swift reply




The query results in access all have 1 single value (sum totals).

Maybe i can pseudo write something like:

Do.Export "Query1" to "excelsheet.xls" into column "B" where row (date) = equal to "FormDateField" in Access

 

[Minty]

Okay - why not have access produce the date value as well?
Much much easier than trying to match the existing spreadsheet.

 

[MushroomKing]

Thanks Minty. I understand this would be a solution.

In this case it's not. I'm exporting to a really complicated file which is a template for the company to generate reports.

I just want to fill in the rows where the date is the same as on my access form.

 

[Gasman]

Export to a second sheet and do a vlookup from your sheet?

Hi everyone!

I've been trying to export queries to excel in vba. Which works alright but needs some work still.

I am completely stuck on where to start when it comes to exporting the data in dynamic (date) cells. Let me explain:

- I have multiple queries in Access. (Qry1, Qry2, Qry3)
- I have an Excel workbook with a sheet that needs to be filled out.
- I have a date column in Excel
- My exported queries are filling in the rows.

This works, but i want to export to the row with the same date that i specified in access!

So if my dates in Excel are in column A, and the date in the Access form field = equal to the date in Row 1 in Excel, i want to export to:

Qry1 > B1
Qry2 > C1
Qry3 > D1

If that makes sense :S. So far i only have this:

DoCmd.TransferSpreadsheet acExport, 8, "Qry1", "C:\accesstool...", True, "B1"

Can somebody please help me with this? I have no clue.
Also would like to keep it simple, im not an Access guru really.

 

[MushroomKing]

Alright! MAYBE that could be an idea ofcourse.

Let me try this .

How do i export multiple queries in the below code?

DoCmd.TransferSpreadsheet acExport, 8, "Qry1", "C:\accesstool...", True, "B1"

I tried some alternatives but my syntaxes are not right.

Thanks for your help!

 

[Gasman]

If they are the same structure, I expect you could use a UNION from all your queries to a single query and transfer that.?


If they are not, then I expect one query to a sheet named the same (for ease of use) and adjust the formula to suit.?

 

[MushroomKing]

Not the same at all no .

DoCmd.TransferSpreadsheet acExport, 8, "Qry1", "C:\accesstool...", True, "B1"

I can not just add another query to that??

 

[Gasman]

Not the same at all no .

DoCmd.TransferSpreadsheet acExport, 8, "Qry1", "C:\accesstool...", True, "B1"

I can not just add another query to that??

I expect so, as I said have a sheet for each query.
However can you not just join the queries by date for one final query and transfer that.?
So you'd select field from Qry1 as qry1, field from Qry2 as qry2 etc.?

 

[MushroomKing]

Thanks gasman! It works now.

Because of this solution, one little problem has risen.

DoCmd.TransferSpreadsheet acExport, 8, "query", "C;\...", True, "cellrange"

Is it maybe possible, every time i run this code, to put it underneath the last row? Instead of overwriting it every time.

This would save me a TON of work. So it has to check if the row is null or whatever and if not, go to next row.

I have no clue since i dont know how to check for that in excel first before running the export. Your help would be awesome!