Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Back to Visitor Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
Parameter VBA for Access Excel
Michael Mifkovic 

17 days ago
I have a parameter access query that prompts the user for a "location" and then the "start" and "end" dates to search for files in that location.  I keep running into issues extracting the data into an existing XLSM (excel) file with built in macros.  I looked through your tutorials and couldn't find one that covered this.  Can you help me out?
Alex Lewis  @Reply  
      
16 days ago
Do you have screenshots of your issue? What does your query look like?
Michael Mifkovic OP  @Reply  

16 days ago
Lewis-sent you a response with screenshots attached this morning.  Just wanted to make sure you got it.
Richard Rost  @Reply  
           
15 days ago
Michael, what exactly do you mean? It's not possible for visitors to directly contact other users, or post screenshots. If you need more help, you'll need to describe the problem in more detail. For example, what do your macros look like? I don't think I've covered inserting data from Access into an existing Excel sheet, but this could be a good topic for a new video.
Michael Mifkovic OP  @Reply  

15 days ago
Lewis:  I am new to using any type of forum.  This page does not allow me to attach any screenshots.  What is the best way get this information to you?  If I need to join to get this handled, I am more than willing to do that.
Thanks
Richard Rost  @Reply  
           
15 days ago
Michael, we do not allow users to contact other users. If you're looking to hire a consultant see the Developer Network page. Otherwise you can describe your issue here.
Michael Mifkovic OP  @Reply  

15 days ago
Okay. Here we go.
Problem: Bring drill hole data from access into a format to be uploaded to Google Maps for others to utilize. Key point-all drill holes have a unique GPS (latitude/longitude), but can have multiple data points at different depths. Note, Map cannot have duplicate GPS.
Issue: Existing form button uses two queries to get the data to excel where 4 separate macros are used to remove duplicate data for export to Google Map.
Start:  1st query uses underlying tables and has two fields that prompt user for information. 1st field is an short text field field for user to specify the area to search (like a county), the second field is a Date/Time field that prompts user for start and end dates.
2nd query uses first query and sorts only on data necessary for Google maps. All GPS is conjuncted into a field called GPS=[latitude]&","[longitude] and is the primary sort. The form button runs the 2nd query.
This works and opens up an Excel.xlxs file. Which was great for the initial construction of the map which I did. I am now retired, and I need someone to take over data conversion to continue on a weekly basis to add new points to the map. It has to be simple enough that someone with no access or excel macro experience can do. So I need to export the data from access to an existing Excel.xlsm file with built in macros to simplify the process.
VBA:Public Sub ExportParameterQueryToExcel()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim ExcelApp as Object
    Dim Wbk As Object
    Dim Wks As Object
     Const EXCEL_PATH As String=C:\test.xlsm
     Const SHEET_NAME As String="Export GPS"
     Const QUERY_NAME As String="04Export GPS"
    
     Set db=CurrentDb
     Set qdf=db.QueryDefs(query_name)
    
     qdf.Parameters(1)="district"  'the area field name
     qdf.Parameters(2)="start_date]"   'the field name is [date_received]
     qdf.Parameters(2)="end_date]"   'the field name is [date_received]
Michael Mifkovic OP  @Reply  

15 days ago
I was cut short, so here is the last of the relevant portion of the code
qdf.Parameters(1)="district"  'the area field name
     qdf.Parameters(2)="start_date]"   'the field name is [date_received]
     qdf.Parameters(2)="end_date]"   'the field name is [date_received]

    set rs=qdf.OpenRecordset()

I get errors regarding the parameter and rs statements.
One of the parameter statements is "item not found" which also will apply to the rs statement.  Earlier error messages were about insufficient parameters or parameters no recognized.  Sorry, I didn't write down the messages, just kept trying to change things to make it work.
Add a Reply Upload an Image
Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/6/2026 7:39:50 PM. PLT: 1s