Discussion Locked

Open Excel workbook & close from Access

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am having trouble with a pocedure. I need to open a specific Excell file to refresh data that my Access program uses. I want to open the sheet (hidden if possible) and after the data is refreshed the sheet is then closed.

3 replies

by miket

Here's some code to access Excel spreadsheets:

Dim App As Excel.Application
Dim Workbooks As Excel.Workbooks
Dim book As Excel.Worksheet

Set App = New Excel.Application

App.Workbooks.Open FileName:=txtSpreadsheetFileName

Set book = App.Worksheets.Item(1)

book.Range("A1").Select

--Your code--

App.Workbooks.Close

Set Workbooks = Nothing
Set App = Nothing

You could also import the spreadsheet into a temporary table then use that table to update your live database:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName", txtSpreadsheetName, True

This discussion is closed to new replies.