Automating export from access qry to excel

[ejc00]

Hey everyone,

Any help on the following problem would be excellent because I have a deadline coming up...

PROBLEM: I'm exporting an Access query to an Excel workbook as a worksheet. I'm using the following code:

strDocName = "qry_Power_Positions_Crosstab_ELE"
strFile = "C:\Data\BO Automation\Power Positions\East Power Positions-062802.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDocName, strFile, True, ""


I've done this for several queries/workbooks it always works fine. However, a couple of export attempts produce errors.

THE ERROR: After the export I try to open the spreadsheet (manually--the open function is not automated). I get this dialog box:

"The instruction at "0x3004c309" referenced memory at "0xfffffffc". The memory could not be "read" Click OK to terminate Click CANCEL to debug the program"

Both options result in excel not being able to open the document.

Any help would be greatly appreciated.

 

[ETID]

Maybe this an option for you?


You would not need to export the data to excel, (too many steps)...

Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb, table or query)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properies to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current access data comes in (if you set the refresh on open option)

 

[xlbo]

ugh - don't know about the error but why not try another way round:
1: add a make table query to your database to make a table out of the crosstab and export thjat instead
2: Same as above but link it to the spreadsheet - no exports needed - instant updates
3: From excel, use Data>Get External Data. Connect to the database and bring the data back through that
HTH
~Geoff~

 

[ejc00]

Thanks for your replies...but this must be done in Access.
If anyone knows anything about the error please let me know...Thank you very much.

I tried the MS Query approach but it said my database had no visible tables.

 

[PeterMoran]

Hi All,

This problem camn be resolved by running Excel VBA in Access. To to this you have to register the Excel Type Library.(Tools - References - Microsoft Excel 9.0 Object Library.


Once you have done this then you can run Excel Code in Access.

Public Function CreateMonthlyFIReports()

Dim objAppXLS As Excel.Application
Dim db As Database
Dim stateWB As Workbook
Dim rs As Recordset
Dim rsGroupedAgent As Recordset
Dim rsSelectAgentNo As Recordset
Dim rsAccept As Recordset
Dim rsDecline As Recordset
Dim rsSheetName As Recordset
Dim f As Field
Dim ws As Workspace
Dim shtAgent As Worksheet
Dim rr As Range
Dim strSQL As String
Dim sheetname As String
Dim AgentState As String
Dim strAgentNos As String
Dim strAgentFortis As String
Dim SavePath As String
Dim i As Integer
Dim rowCount As Integer
Dim Accept_Count As Integer
Dim Decline_Count As Integer
Dim Aper As Double
Dim Dper As Double


DoCmd.Hourglass (True)

Set ws = CreateWorkspace("", "admin", "", dbUseJet)
Set db = CurrentDb()


Set objAppXLS = New Excel.Application

On Error Resume Next
MkDir (PATH_SAVE & MonthName(Month(Now)))

SavePath = PATH_SAVE & MonthName(Month(Now)) & "\"

Set stateWB = Workbooks.Add
stateWB.Activate
stateWB.SaveAs (SavePath & rs![State] & MonthName(Month(Now)))


strSQL = " "
** If you use dynamic SQL. use this syntax.
Set rsGroupedAgent = db.OpenRecordset(strSQL, dbOpenSnapshot)

Or if it's a query (QueryDef)
Set qry = db.QueryDefs("QryName&quot
If you use parameters - you will need to define these as well. NB - this may also be done by name although I use the index.
Set PrmPolicyNo = qryOfferData.Parameters(0)
Set rs = db.qrydef.OpenRecordset("QueryName&quot


Set shtAgent = Sheets.Add()


On Error Resume Next
shtAgent.Name = sheetname
shtAgent.Name = Left(rsSheetName![AgentName], 30)

Details for the Excel Spreadsheet data:

Set rr = shtAgent.Range("A1&quot
rr.CopyFromRecordset rsGroupedAgent

Headings from the field names:

shtAgent.Rows(1).Insert
i = 1
For Each f In rsGroupedAgent.Fields
shtAgent.Cells(1, i) = f.Name
i = i + 1
Next f


Format to suit the user's own preferences:

Rows("1:1&quot.Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

rowCount = shtAgent.UsedRange.Rows.Count + 1

shtAgent.Range("A" & CStr(rowCount + 1)) = "Number Accepted:"
shtAgent.Range("A" & CStr(rowCount + 2)) = "Number Declined:"
shtAgent.Range("A" & CStr(rowCount + 3)) = "Total:"
shtAgent.Range("A" & CStr(rowCount + 4)) = "Renewal Rentention:"
shtAgent.Range("A" & CStr(rowCount + 5)) = "Percentage Declined:"

shtAgent.Range("A" + CStr(rowCount + 1), "A" + CStr(rowCount + 5)).Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A1:AX1&quot.Select
Selection.ColumnWidth = 25

Range("D1&quot.Select
Selection.ColumnWidth = 40

Range("P&quot.Select
Selection.NumberFormat = "dd/mm/yyyy"

Range("AE:AW&quot.Select
Selection.NumberFormat = "$#,##0.00"
Range("A1&quot.Select

rs.MoveNext
Wend


stateWB.Save
stateWB.Close

Set rs = Nothing
Set rsGroupedAgent = Nothing
Set rsAccept = Nothing
Set rsDecline = Nothing
Set rsSheetName = Nothing
Set rsSelectAgentNo = Nothing

Set objAppXLS = Nothing

DoCmd.Hourglass (False)
End Function


I use this often to create Excel Reports from Access. Note that the Workbook, and worksheet can be named. And I use DAO, so if your using Access97 the sytnax will be the same.

I hope this is useful - because if you can impletment it the user's will be REALLY impressed.

Regards,

Michael P.