Opening Files

[Dave]

Hi

Could someone provide me with code to do the following please:

While using Access I and need to open an Excel spread sheet then save it. I
can do this using Excel only, but need to do this within Access

Please help


Dave

 

[Tom Ogilvy]

Look in VBA help for createobject and getobject

Dim xlApp as Object
Dim xlBook as Object
Dim xlSheet as Object
set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.Workbooks.Open "C:\My Folder\MyFile.xls"
' or
'Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Cells(1,1).Value = 1
xlBook.Save
' or xlBook.SaveAs "C:\My Folder\MyFile1.xls"
xlBook.close SaveChanges:=False
set xlsheet = nothing
set xlBook = nothing
xlApp.Quit
set xlApp = Nothing

some references:
http://support.microsoft.com/support/OfficeDev/FaqVBOffice.asp
Frequently Asked Questions about Microsoft Office Automation Using Visual
Basic


http://support.microsoft.com/support/OfficeDev/offdevinapps.asp
Programming Office from Within Office

http://support.microsoft.com/support/officedev/iisfaq.asp
Integrating Office with IIS FAQ

http://support.microsoft.com/?id =219151
Q219151 - HOWTO: Automate Excel 97 and Excel 2000 from Visual Basic

http://support.microsoft.com/support/excel/content/Automation/automation.asp
Using Automation with Microsoft Excel 97

http://support.microsoft.com/?id=153748
ACC: How to Call Excel Functions from Within Microsoft Access

Automating Excel from VB and from Access is identical.

 

[Tom Ogilvy]

Here are two more:

http://support.microsoft.com/?id=153748
ACC: How to Call Excel Functions from Within Microsoft Access

http://support.microsoft.com/?id=167223
Microsoft Office 97 Automation Help File Available on MSL