Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2018, 12:54 AM   #1
MushroomKing
Newly Registered User
 
Join Date: Jun 2018
Posts: 45
Thanks: 20
Thanked 0 Times in 0 Posts
MushroomKing is on a distinguished road
Access export queries to excel, with date equal to date in row

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:

Code:
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 is offline   Reply With Quote
Old 07-13-2018, 01:21 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,596
Thanks: 134
Thanked 1,513 Times in 1,485 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Access export queries to excel, with date equal to date in row

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-13-2018, 01:42 AM   #3
MushroomKing
Newly Registered User
 
Join Date: Jun 2018
Posts: 45
Thanks: 20
Thanked 0 Times in 0 Posts
MushroomKing is on a distinguished road
Re: Access export queries to excel, with date equal to date in row

Thanks for the swift reply

printscreenexcel.png


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

MushroomKing is offline   Reply With Quote
Old 07-13-2018, 01:45 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,596
Thanks: 134
Thanked 1,513 Times in 1,485 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Access export queries to excel, with date equal to date in row

Okay - why not have access produce the date value as well?
Much much easier than trying to match the existing spreadsheet.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-13-2018, 02:40 AM   #5
MushroomKing
Newly Registered User
 
Join Date: Jun 2018
Posts: 45
Thanks: 20
Thanked 0 Times in 0 Posts
MushroomKing is on a distinguished road
Re: Access export queries to excel, with date equal to date in row

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.
MushroomKing is offline   Reply With Quote
Old 07-13-2018, 02:41 AM   #6
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,398
Thanks: 291
Thanked 374 Times in 359 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Access export queries to excel, with date equal to date in row

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


Quote:
Originally Posted by MushroomKing View Post
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:

Code:
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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-13-2018, 03:02 AM   #7
MushroomKing
Newly Registered User
 
Join Date: Jun 2018
Posts: 45
Thanks: 20
Thanked 0 Times in 0 Posts
MushroomKing is on a distinguished road
Re: Access export queries to excel, with date equal to date in row

Alright! MAYBE that could be an idea ofcourse.

Let me try this .

How do i export multiple queries in the below code?

Code:
DoCmd.TransferSpreadsheet acExport, 8, "Qry1", "C:\accesstool...", True, "B1"
I tried some alternatives but my syntaxes are not right.

Thanks for your help!

MushroomKing is offline   Reply With Quote
Old 07-13-2018, 03:30 AM   #8
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,398
Thanks: 291
Thanked 374 Times in 359 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Access export queries to excel, with date equal to date in row

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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-13-2018, 03:47 AM   #9
MushroomKing
Newly Registered User
 
Join Date: Jun 2018
Posts: 45
Thanks: 20
Thanked 0 Times in 0 Posts
MushroomKing is on a distinguished road
Re: Access export queries to excel, with date equal to date in row

Not the same at all no .

Code:
 
DoCmd.TransferSpreadsheet acExport, 8, "Qry1", "C:\accesstool...", True, "B1"
I can not just add another query to that??
MushroomKing is offline   Reply With Quote
Old 07-13-2018, 07:33 AM   #10
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,398
Thanks: 291
Thanked 374 Times in 359 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Access export queries to excel, with date equal to date in row

Quote:
Originally Posted by MushroomKing View Post
Not the same at all no .

Code:
 
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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-16-2018, 11:28 PM   #11
MushroomKing
Newly Registered User
 
Join Date: Jun 2018
Posts: 45
Thanks: 20
Thanked 0 Times in 0 Posts
MushroomKing is on a distinguished road
Re: Access export queries to excel, with date equal to date in row

Thanks gasman! It works now.

Because of this solution, one little problem has risen.

Code:
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!

MushroomKing is offline   Reply With Quote
Reply

Tags
cells , date , equal date , excel , export

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] EXPORT TO EXCEL - time become date? Onlylonely Modules & VBA 6 06-07-2018 07:19 PM
[SOLVED] how to export cross tab queries by date parameters beginner_access Queries 3 02-23-2015 07:47 PM
2 Table date fields that can be a date or null - determine if they are they equal Rx_ Modules & VBA 7 02-08-2013 11:31 AM
Date formatting on access export to excel nscotty_2000 Modules & VBA 11 11-10-2010 01:46 PM
Export Access Table to Excel- with the date as part of the new Excel file name WinDancer Macros 10 01-22-2008 01:10 PM




All times are GMT -8. The time now is 05:03 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World