UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export Access Query To Excel Template With VBA, Access 2003    
 
   
foxtrojan
post Jul 6 2018, 10:16 AM
Post#1



Posts: 1,108
Joined: 8-May 06
From: Singapore


Have gone through numerous postings, tried dozens of examples but still unable to automate export my query to a formatted Excel spreadsheet. I am able to export to a new worksheet but have to "cut and paste" to a formatted worksheet. Appreciate very much if anyone can help me with a workable, error free example.?
Go to the top of the page
 
theDBguy
post Jul 6 2018, 10:17 AM
Post#2


Access Wiki and Forums Moderator
Posts: 72,704
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Have you tried using CopyFromRecordset?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
foxtrojan
post Jul 6 2018, 02:07 PM
Post#3



Posts: 1,108
Joined: 8-May 06
From: Singapore


Hi DBguy, thanks for your reply. Do you have the sample codes using the "Copyfromrecordset"?
Go to the top of the page
 
theDBguy
post Jul 6 2018, 02:15 PM
Post#4


Access Wiki and Forums Moderator
Posts: 72,704
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Take a look at this previous thread.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
foxtrojan
post Jul 8 2018, 08:57 AM
Post#5



Posts: 1,108
Joined: 8-May 06
From: Singapore


No, DBguy the thread does not work. I think the main problem is the QUERY to be exported cannot contain parameters, or any criteria[s]. Any samples of such codes or functions that works?
Go to the top of the page
 
ADezii
post Jul 8 2018, 09:31 AM
Post#6



Posts: 1,958
Joined: 4-February 07
From: USA, Florida, Delray Beach


Should all else fail, you could always perform the Export to Excel, then Format the Spreadsheet from Access via Automation Code.
Go to the top of the page
 
foxtrojan
post Jul 8 2018, 10:47 AM
Post#7



Posts: 1,108
Joined: 8-May 06
From: Singapore


Thanks Adezii. Yes that's a good point there, Appreciate very much if you could give me a sample.
Go to the top of the page
 
ADezii
post Jul 8 2018, 01:36 PM
Post#8



Posts: 1,958
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I created a simple Demo for you using Early Binding (just my favorite, you could Late Bind) that will:
    1. Open a Workbook named Test.xls in the C:\Test\ Folder, namely C:\Test\Test.xls.
    2. Make the Worksheet named 'MAIN" the Active Sheet.
    3. Set the Font to Bold in Range(A3"A12") in Worksheet MAIN.
    4. Set the NumberFormat in Column 3 ('C') to "#,##0".
    5. AutoFit Column 3 ('C').
    6. Close the Workbook and Save the Formatting changes that were made.
    7. Perform standard Clean Up chores.
  2. Code Definition:
    CODE
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet

    Set appExcel = New Excel.Application
        appExcel.Visible = True      'For Debugging purposes
    Set wkb = appExcel.Workbooks.Open("C:\Test\Test.xls")
    Set wks = wkb.Worksheets("MAIN")

    wks.Activate

    With wks
      .Range("A3:A12").Font.Bold = True
        With .Columns(3)
          .NumberFormat = "#,##0"
          .AutoFit
        End With
    End With

    wkb.Close True      'Close Workbook and Save
    appExcel.Quit
    Set appExcel = Nothing
    Set wkb = Nothing
    Set wks = Nothing
  3. Hope this helps.

This post has been edited by ADezii: Jul 8 2018, 01:36 PM
Go to the top of the page
 
foxtrojan
post Jul 9 2018, 12:28 PM
Post#9



Posts: 1,108
Joined: 8-May 06
From: Singapore


Thanks a million. Sure it is helpful. Will work on it and if encounter problems, will get back to you. Cheers
Go to the top of the page
 
ADezii
post Jul 9 2018, 12:30 PM
Post#10



Posts: 1,958
Joined: 4-February 07
From: USA, Florida, Delray Beach


Good luck with your Project, we'll be here if you need us.
This post has been edited by ADezii: Jul 9 2018, 12:30 PM
Go to the top of the page
 
foxtrojan
post Jul 10 2018, 01:37 PM
Post#11



Posts: 1,108
Joined: 8-May 06
From: Singapore


It works fine other than with a report that takes in a user defined parameter. Is there a way to pass in that user defined parameter during run time?
Go to the top of the page
 
ADezii
post Jul 10 2018, 01:43 PM
Post#12



Posts: 1,958
Joined: 4-February 07
From: USA, Florida, Delray Beach


Please be a little more specific.
Go to the top of the page
 
foxtrojan
post Jul 12 2018, 06:35 AM
Post#13



Posts: 1,108
Joined: 8-May 06
From: Singapore


Sorry, The problem I have encountered was that I cannot have any criteria in my queries, such as Date Range, locations, etc. I need to have a criteria in my query such as a date range to produce a Report for a specified period, such as daily or weekly reports. It is okay in Access but when exported to Excel this is the problem.
Go to the top of the page
 
ADezii
post Jul 12 2018, 06:56 AM
Post#14



Posts: 1,958
Joined: 4-February 07
From: USA, Florida, Delray Beach


You probably could:
  1. Create a Recordset based on the Query.
  2. Evaluate the Parameters.
  3. Export the Query to Excel using CopyFromRecordset.
  4. Format/Save the Spreadsheet.
Go to the top of the page
 
foxtrojan
post Jul 13 2018, 12:09 PM
Post#15



Posts: 1,108
Joined: 8-May 06
From: Singapore


Hi Dezii,
Really appreciate your great help. Yes I have tried that, but sad to say, it still carries the "parameter" error.
Export with a select query, is okay but with a SQL query, it does not work.
Go to the top of the page
 
ADezii
post Jul 13 2018, 12:39 PM
Post#16



Posts: 1,958
Joined: 4-February 07
From: USA, Florida, Delray Beach


Can you post the SQL of your Query?
Go to the top of the page
 
foxtrojan
post Jul 14 2018, 01:41 PM
Post#17



Posts: 1,108
Joined: 8-May 06
From: Singapore


Sure, this Is the SQL query

SELECT [qry3MSalesECommerce].* FROM [qry3MSalesECommerce] IN 'C:\MFE\MFEProgram\MFEProgramUSJ\MFEProgramUSJ.mdb'
UNION ALL
SELECT [qry3MSalesECommerce].* FROM [qry3MSalesECommerce] IN 'C:\MFE\MFEProgram\MFEProgramIPH\MFEProgramIPH.mdb';
Union all
SELECT [qry3MSalesECommerce].* FROM [qry3MSalesECommerce] IN 'C:\MFE\MFEProgram\MFEProgramKuh\MFEProgramKuh2.mdb';
UNION ALL SELECT [qry3MSalesECommerce].* FROM [qry3MSalesECommerce] IN 'C:\MFE\MFEProgram\MFEProgramPNG\MFEProgramPNG.mdb';


The criteria are in the Select Queries [qry3MSalesEcommerce]
Go to the top of the page
 
ADezii
post Jul 14 2018, 02:28 PM
Post#18



Posts: 1,958
Joined: 4-February 07
From: USA, Florida, Delray Beach


Please Post the SWQL of qry3MSalesEcommerce.
This post has been edited by ADezii: Jul 14 2018, 02:28 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th July 2018 - 11:37 AM