Results 1 to 8 of 8

Exporting from Access to Excel via Macro / VBA Help

  1. #1
    ViNNiE is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    4

    Question Exporting from Access to Excel via Macro / VBA Help

    Hi All,



    Long time reader, first time poster.

    I am currently setting up a database that I would like to export a query with 1 column that has parameters set (single date) to an excel template from into a certain cell range.

    I do not have a lot of experience in this field (VBA) but have pieced together something from here.

    Now my issue that this code exports the query without parameters set but as soon as I set the date parameter the code doesn't work.

    I was able to add the first code entry into the second but it only copied the date column across and didn't sort by the parameter.

    Any help fixing this would be greatly appreciated.

    A A Ron

    Code:
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    
    Set db = CurrentDbSet qdf = db.QueryDefs("table1 query")
     
    For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
    Next prm
    Code:
    Public Function Access2XL()
    
    Dim rst
    Dim XL As Excel.Application
    Set XL = CreateObject("excel.application")
    Dim vFile
    
    
    vFile = "\\Mac\Home\Documents\TEST DB\testss.xlsx"
    
    
    Set rst = CurrentDb.OpenRecordset("table1 query")
    
    
    With XL
       .Visible = True
       .Workbooks.Open vFile
       .Sheets("Sheet1").Select
       .Range("c4").Select
       .ActiveCell.CopyFromRecordset rst
       .ActiveWorkbook.Save
    End With
    Set rst = Nothing
    Set XL = Nothing
    End Function
    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,624
    The line to set the recordset would use the qdf variable instead of CurrentDb.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    863
    Try:
    Code:
    Public Function Access2XL()
    
    
    Dim rst as DAO.Recordset
    Dim XL As Excel.Application
    
    
    Dim vFile
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    
    Set XL = CreateObject("excel.application")
    
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("table1 query")
     
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    
    
    
    vFile = "\\Mac\Home\Documents\TEST DB\testss.xlsx"
    
    
    
    
    'Set rst = CurrentDb.OpenRecordset("table1 query")
    Set rst=qdf.openrecordset
    
    
    With XL
       .Visible = True
       .Workbooks.Open vFile
       .Sheets("Sheet1").Select
       .Range("c4").Select
       .ActiveCell.CopyFromRecordset rst
       .ActiveWorkbook.Save
    End With
    Set rst = Nothing
    Set XL = Nothing
    End Function

  4. #4
    ViNNiE is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    4
    Hi Gicu,

    Thank you for your reply.

    I have entered the above and has seemed to fix this problem.

    The next issue I am having however is a runtime error.

    Runtime Error'2482' .Microsoft Access cannot find the name 'Enter Date' you entered in the expression.

    'Enter Date' being the parameter I set in the criteria column on the design view in my query.

    Thanks Again

    A A Ron

  5. #5
    ViNNiE is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    4
    Hi Paul,

    Thanks for your reply.

    I'm not exactly sure wat yo meant by that.

    As said before I dnt have much experience with the VBA side of Access.

    Thanks

    A A Ron

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,624
    Gicu gave you the code i meant. The code is meant to handle form parameters, so you're getting an error. It doesn't know what value you want to enter. I'd have the user enter a date in a form textbox and have the query get it from there. Most of us avoid the bracketed parameter method.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ViNNiE is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    4
    Hi All,

    Just an update.

    I have got it working by using a form to run the script and take the date needed.

    Thank you both for your help Paul and Gicu.

    A A Ron

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,624
    Happy to help and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting data from Excel to Access
    By victorythroughharmony in forum Access
    Replies: 2
    Last Post: 03-13-2018, 07:31 AM
  2. Replies: 2
    Last Post: 09-26-2017, 09:18 AM
  3. Exporting Excel to Access
    By Fletch161 in forum Access
    Replies: 2
    Last Post: 08-12-2015, 12:54 PM
  4. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  5. Replies: 1
    Last Post: 04-05-2012, 01:08 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums