Connecting Access and Excel 3

[ScottXJ]

Hello,

I have an Access 2000 application that exports table data to an Excel spreadsheet. The spreadsheet is then manually edited in order to meet corporate standards before it is posted on the intranet. I have created a macro in the Excel spreadsheet in order to eliminate the manual editing process. However, what I would like to be able to do is to export the Access table data to the Excel spreadsheet and automatically execute the macro to edit the spreadsheet format, thereby, eliminating any user intervention. Is this possible? Is there a way through code to set-up the spreadsheet format before the data is passed from the Access application?

Any assistance would be appreciated.

Thanks,

Scott.

 

[sfreeman]

Try linking to the spreadsheet instead of exporting to it. Run your update code in VBA within Access.

Sam_F
"90% of the problem is asking the right question.

 

[Blorf]

Sam, can you write to an linked excel sheet from access? If so, How?

Thanks,
ChaZ

 

[sfreeman]

Yes, you can. Just treat it like any other table. You will have a problem 'deleting' records, not supported by that ISAM, but there is a work around for that.

See link for more info:

http://www.vb123.com/toolshed/98docs/excelbe.htm

Sam_F
"90% of the problem is asking the right question.

 

[AccessGuruCarl]

Hi ScottXJ,

Here is some code, found here on the site, that is modified for your needs.

In Access
Set a reference to Excel...
Create a new module, paste this code.

Public Sub OpenExcel(strSource As Variant)

Dim msExcel As Object
Dim strDefaultSheet
Dim strMacroSheet As String
    strDefaultSheet = 1         'use index property to open 1st sheet
    strMacroSheet = "MyMacro"   'This sheet runs macro on activate
On Error Resume Next
Set msExcel = GetObject(, "Excel.application")
If Err.Number <> 0 Then
  ' no instance of Excel, create
  Err.Clear
  Set msExcel = CreateObject("Excel.application")
  If Err.Number <> 0 Then
    ' Excel installed???
    Exit Sub
  End If
End If
On Error GoTo ERR_OpenExcel
    msExcel.Visible = False
    msExcel.Workbooks.Open strSource
' What worksheet(tab) do you want active
'Run the macro sheet
    msExcel.Worksheets(strMacroSheet).Activate  
'Re-Activate the default sheet, Excel remembers last sheet opened.
'FYI
'In this case if MyMacro was orginally named Sheet3, 
'when Excel re-opens Sheet2 would be the active sheet.    
    msExcel.Worksheets(strDefaultSheet).Activate    
' Close / Exit Excel
    msExcel.Quit
    Set msExcel = Nothing
ExitingSub:
Exit Sub

ERR_OpenExcel:
    MsgBox Err.Number & vbCr & Err.Description, vbExclamation, "Error Opening Word"
    Err.Clear
    Resume ExitingSub
       
End Sub

Now, In Excel...
Create a new worksheet, name it MyMacro
Open the VBE for Excel
Double Click the MyMacro code page.
Add this event.

Private Sub Worksheet_Activate()

    'Paste your macro code here

End Sub

Next, Double Click the ThisWorkbook code page
Paste this code,

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Save the workbook, or you'll get a prompt, 
'when running the code from MS Access
    Me.Save
End Sub

Private Sub Workbook_Open()
'Hide the macro sheet, so other users don't open it!
    Sheets("MyMacro").Visible = False
End Sub

Hope this helps...
Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[ScottXJ]

Good morning Sam_F and Carl,

Thanks for your quick responses. I will be working on testing out both of your suggestions this morning.

Thanks a lot guys and I will keep you posted as to how these suggested solutions work out.

Thanks again,

Scott.

 

[avayaman]

Just call your excel macro auto_open & it will execute as soon as you open the excel spreadsheet.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail use form on website at

http://www.vancouver.hm/email.html