Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 6th, 2006, 11:57 AM
Registered User
 
Join Date: Jun 2006
Location: , , Bahrain.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Export an Access query to Excel

Please help me

  I need to export an access query to excel that can do two things.

1st : The ability to filter the query by date, that I will enter in the control box of the Access form

2ND : During exporting it should filter -for example- the Country field automatically
Meaning to say that any record having the USA country name should be in one sheet and the sheet's name should be USA …

And any record having the UK country name should be in another sheet and the sheet's name should be UK, and so on, filtering all the country field.

Is it possible?


 
Old June 6th, 2006, 12:15 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Well, I can get you started. I don't have my "Integrating Access and Excel" (O'Reilly) book with me today, but I will look up the second issue if no one else posts.

First, create your query that will be filtered by date.

Second, create your form with the control on it to filter the query, and the button to launch the export.

Third, in the date field of your query, reference the control where the date will be entered, like this:

[Forms]![frmYourFormName].[ctlYourControlName]

Fourth, do some error checking in your button code so that a null value is not sent to the export function, and stop the sub and pop up a message box if no date is entered. One simple way to do this is to put today's date in the control by default. Also, use the date field in the underlying table to populate a combo box for the date control, that way invalid dates can't be entered.

Fifth, write code that queries your query and pulls a recordset of the country codes based on the date you select in your form. This will make sure that only those countries in the dated report will have a worksheet created for them (rather than all countries, unless you want this).

Now you will be all set up with the elements you need to push the data to a spreadsheet with sorted worksheets.

If no other poster can finish the routine, I will look this up and write an answer tonight or tomorrow am.

HTH


mmcdonal
 
Old June 7th, 2006, 07:00 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

How is this going? I have the Access and Excel reference. As it turns out, this is a fairly straightforward process. I think you will need to run the query as many times as there are country codes for the date selected, and pass each one of the recordsets to a new worksheet each time. To get you started, her is how you would get Excel open and create a new workbook and new worksheets:

Public Sub GetExcel()
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Set xlApp = New Excel.Application
xlApp.Visible = True 'or false in your case?
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets.Add
xlWs.Name = "CountryCode"
End Sub

This will create the worksheet with the country code, and then leave the app open.

Let me know when you want to get into this.

mmcdonal
 
Old June 7th, 2006, 07:04 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

In trying this I got an error, but then I selected the Microsoft Excel Object Library and it works fine.

This may be problematic when giving this app to other users since you will need to make sure they have the proper reference selected.

mmcdonal
 
Old June 7th, 2006, 04:29 PM
Registered User
 
Join Date: Jun 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I was able to accomplish the same thing using code that I downloaded from this site.
Now I have 2 more issues that I need help with-
1)setting up an auto-email of the resulting .xsl file to a list of four people
2)saving the resulting .xsl file to a location and overwriting it with updated data when the code is automatically triggered 2 times daily
Do you think you might have any suggestions on how to accomplish these tasks?
Any assistance is greatly appreciated. :)

The basic code I am using is:

Private Sub cmdExportExcel_Click()

Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
strQueryName = "Samples"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)

Set xlSheet = xlWorkbook.Sheets(1)
    For lvlColumn = 0 To objRST.Fields.Count - 1
      xlSheet.Cells(1, lvlColumn + 1).Value = _
      objRST.Fields(lvlColumn).Name
    Next
    'Change the font to bold for the header row
    xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
    'Add a border to header row cells
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

With xlSheet
    .Range("A2").CopyFromRecordset objRST
    .Name = Left(strQueryName, 31)
End With
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing

End Sub
 
Old June 8th, 2006, 06:36 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This looks good, but you are only creating one sheet? I thought you needed more. I can't see where you are saving the file. Is this being done with code, or is the user saving the file once it pops up? If with code, just set overwrite to true.

mmcdonal




Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Access queries to Excel Spymaster Access VBA 2 August 18th, 2006 11:21 AM
Export from Access to Excel in ASP generated page u0107 Classic ASP Basics 1 July 16th, 2006 05:25 AM
Export Access Paramter Query to Excel jcharlton VB Databases Basics 1 February 10th, 2006 08:58 AM
Export Mircosoft Excel Data to Access Table tiyyob Excel VBA 0 January 11th, 2006 06:19 AM
Export Table from Access to Excel aramchan Access VBA 2 July 12th, 2004 03:20 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.