Run Excel Module from Access

A

AEROTJK

are there any VBA functions that would allow me to execute an excel module
from access??
 
R

ryguy7272

This is how I do it:

Option Compare Database

Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub



Private objExcel As Excel.Application

Private xlWB As Excel.Workbook

Private xlWS As Excel.Worksheet



Sub Rep()

Dim strFile As String

strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls"

‘Of course, this is just an example; put the actual path to your actual file
here…

' Opens Excel and makes it Visible

Set objExcel = New Excel.Application

objExcel.Visible = True

' Opens up a Workbook

Set xlWB = objExcel.Workbooks.Open(strFile)

' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.

Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")

With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here…you can record a macro and make the process
super easy!!

End With

' Close and Cleanup

xlWB.SaveAs xlSaveFile

xlWB.Close

xlapp.Quit

Set xlapp = Nothing



End Sub


Send me an email if you have any questions about controlling Excel from
Access!!

HTH,
Ryan---
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top