Export to excel many problems

[Costefran]

Can anyone please help. I have been trying to export data from a saved query to excel with no luck


I have tried using the output to and transfer spreadsheet method to an excel spreadsheet but get a 'Null Pointer Problem in excel so I now have code which opens the spreadsheet and the spreadsheet has a database query which pulls data from the connected query / table

When the spreadsheet is connected to a query I get a message saying too few parameters expected 1
However this works fine if I pull data from a table

Is there a better way?

Thanks

 

[kjv1611]

It sounds like the query in your Access database is a parameter query.

I suppose the (not cleanest) but easiest fix for now would be to create a basic SELECT query off of the existing query you're trying to output, and then export that to Excel, or use Excel to import the data..

So if your current query is qryParameterQuery, then you could create a new query called qryExport with this as it's source:

SELECT p.*
FROM qryParameterQuery p

Just one thought, anyway.

Otherwise, look into when you're exporting the query... is the parameter able to pull the values it's looking for? Say from a certain form or whatever? If the form it's referencing, for isntance, is closed, then the parameter can't return the expected value. In that case, you might need to run an Update, Append, or Create table query to build a static data source to totally get around the issue.

 

[Costefran]

Thanks for your response. However, I think I've almost cracked it except the data cannot be seen in the excel spreadsheet


I now have code which creates a table as follows and this works fine
If Forms![Log In Form]![Record or Report2] = 3 And Forms![Log In Form]![Start Date Count] = 1 Then DoCmd.RunSQL "SELECT [Record_Report Query with Start Date6].* INTO [" & Forms![Log In Form]![Name] & " Table" & "]FROM [Record_Report Query with Start Date6]"

'DoCmd.SetWarnings (True)
If Forms![Log In Form]![Record or Report2] = 3 And Forms![Log In Form]![Start Date Count] = 1 Then Call OpenSpecific_xlFile


I then run the open specific XL file function with code as follows but as said there is not data in the excel sheet. Also I would like to open a specific excel spreadsheet called "Maintenance.xls" but due to my lack of knowledge regarding this code I'm not sure where this should go

Function OpenSpecific_xlFile()
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _
"C:\database.mdb")
Set db = CurrentDb
Set rs = db.OpenRecordset("" & Forms![Log In Form]![Name] & " Table" & "", dbOpenSnapshot)

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset
'rs.Close
'db.Close
End Function

 

[kjv1611]

In that case, it appears you are never importing the data in excel. Here's what your code is doing:

1. Creating the new table from the SELECT INTO query.
-Which by the way, you may want to include some conditional code before this to make sure the table does not exist, else it'll error out.
2. Creating database and recordset objects to represent the new table values.
3. Opening Excel, and adding a new worksheet, then setting your worksheet variable equal to the first worksheet in the workbook.

But there's no time shown where you're actually grabbing the data from Access.

What would be best, I think, would not even require the Excel objects, or even the database and recordset objects...

I'll get that together shortly, and show you what I mean..

 

[kjv1611]

Here we go, take a try at this (modifying as need be):

1. Create an Excel workbook that will be grabbing the data from Access. Import the access table you want to use as the standard table for this operation as the data source - under external data sources in Excel.

2. With Excel set to import the data, set the option for "refresh on open"

3. Save this workbook as a template - be sure to save it in a location that you and/or others who may need it can get to it. And if it were me, I'd close the file when ready, and check the file attribute, "hidden", and perhaps "read only" when you've finished editing it. This will keep anyone else from accidentally editing it or deleting it.

4. Set up your code that can run each time, and give you the results you need without a lot of headache. For your purposes, I don't think it really matters what the table name is, you just need to get it out. Therefore, I'd use a static table name, and simply refresh the data in said table on each occasion via code. It's easier than it sounds, and probably will run faster anyway.

For the example code, I'm going to name the table, tblExport

Private Sub ExportMyData()
  Dim strSQL As String 'one SQL variable to rule them all!
  Dim strXl As String 'file path to your Excel Template
  Dim appXl as Excel.Application

  Set appXl = New Excel.Application
  strXl = "C:\MyExcelTemplate.xlt" 

  If Forms![Log In Form]![Record or Report2] = 3 And Forms![Log In Form]![Start Date Count] = 1 Then
[GREEN]'First, run some SQL to clear out any existing data in the export table.[/GREEN]
    strSQL = "TRUNCATE TABLE tblExport"
    DoCmd.SetWarnings FALSE
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings TRUE

[GREEN]'Next, run your SQL to populate the table.[/GREEN]
    strSQL = "INSERT INTO tblExport SELECT a.* FROM [Record_Report Query with Start Date6] a"   
    DoCmd.SetWarnings FALSE
    DoCmd.RunSQL
    DoCmd.SetWarnings TRUE
  End If

[GREEN]'Open the Excel Template which will create a new Excel file in the process, but already have your data in place.[/GREEN]
  With appXl 
    .Visible = True 
    .Workbooks.Open (strXl)
  End With 

[GREEN]'Clean up variables - yeah, the last 2 are probably not necessary, but I just like cleaning up as much as possible.[/GREEN]
  Set appXl = Nothing
  strXl = vbNullString
  strSQL = vbNullString

End Sub

Of course, the Excel file path would be different to whatever you want to use. Also, you can save the new workbook in code if you want, but if you're going to open it anyway, you can also leave that part out.

Also, you CAN run this as a Function if you prefer, but if you're not feeding any values to it in code, I don't see the purpose/need. But to each his/her own.

 

[Costefran]

Hey thats great and now works

Many thanks for the help