Opening Excel from Access

[danno_mbk]

Sorry guys I am new to Access, and dont know it as much as Excel.

Is it possible to open a specific Excel workbook in from Access 97 ?

I have a macro running in acess that needs to export to excel to do some calculations, which will automatically run using a macro that starts when teh work book is openend do the do then save and close

Access will then import the file back in and use that and carry on with its own macro..

can anyone help cause there are 3 of us here that are quite confused !

thanks

Danno

 

[Norie]

Danno

Could you not do the required calculations in Access?

 

[danno_mbk]

we have tried , but its doing calculations on various lines, access doesnt like that but excel does it with a little formula really quickly.

We just wanted Access to export a table in excel format, have it open excel and the specific file (once opened the macro will run automatically).

I think that is as far as Access goes, we will then have to run another macro in Access to finish to process off. ??

But if anyone has any ideas as how to run excel within Access it would be helpfull, cause we are seriously stuck !
thanks

 

[Tanis]

Norries right. Could you not do the calculations in Access? In case you can't, use this code behind a command button.
I use this code in my application with no problems. Not sure where I got it from, so can't credit the author.

Private Sub ExportToExcel_Click()
On Error GoTo Err_cmdExportToExcel_Click


Dim stDocName As String
DoCmd.SetWarnings False

stDocName = "Your Query name"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

'Copy records to rows
'in an existing Excel Workbook and worksheet
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer

Const conSHT_NAME = "Sheet Name"
Const conWKB_NAME = "Path to Excel.xls"

Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("Table Name", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range("A1").CopyFromRecordset rs
End With
End With

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing


Exit_cmdExportToExcel_Click:
Exit Sub

Err_cmdExportToExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportToExcel_Click
End Sub

 

[Norie]

Danno

What calculations are you actually doing?

If it's on particular 'lines' you could create queries that only return those required.

Tanis

Could you not just use the TransferSpreadsheet method to export a table to Excel?

 

[danno_mbk]

Thanks for that, I will try the code. We couldnt figure out how to make access do what we wanted, but as I know excel pretty well, we used that instead,.. access is not my strong point !
thanks or your help

 

[Tanis]

I suppose you could. Does the TransferSpreadsheet method give you as much control?

 

[Norie]

What control does the posted code give?

As far as I can see it exports to a new worksheet in an existing workbook.

That's basically what TransferSpreadsheet does.

 

[Tanis]

Hi Norrie. Yeah, the TransferSpreadsheet method does almost what I want, but I do need to specify a specific worksheet. I don't think the TransferSpreadsheet method does that, or am I wrong?

 

[mdmilner]

CopyFromRecordset lets you be a little more specific about where you want to put things than TransferSpreadsheet.

Like you, Norie, I'm thinking they could easily have done the calculations inside Access.

Just in case you guys are interested at all in how to do it in Access -- check out Crosstab queries. You can also create your own summation queries providing averages/min/max values depending on what you need exactly.

Mike