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?

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

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

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

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

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