How can I read an excel sheet from AccessCan I

Hi @Jackie Beedie ,

Thanks for reaching out.

Based on what I found, it does look like you should be able to read data from an Excel sheet in an Access VBA procedure. If your goal is to bring the sheet into Access and work with it like normal table data, DoCmd.TransferSpreadsheet seems to be the most straightforward approach, since it imports the worksheet directly into an Access table.

Sub ImportExcelSheet()
  DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
    TableName:="ImportedExcelData", _
    FileName:="C:\Temp\MyWorkbook.xlsx", _
    HasFieldNames:=True, _
    Range:="Sheet1$"
End Sub

That should import Sheet1 into a table called ImportedExcelData, which seems to be the easier option if you want to query or process the rows in Access.

If you do not want to import the sheet and just need to open the workbook and read values directly, it looks like you could also do that through Excel automation from VBA instead.

Sub ReadExcelCells()
  Dim xlApp As Object
  Dim xlBook As Object
  Dim xlSheet As Object

  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open("C:\Temp\MyWorkbook.xlsx")
  Set xlSheet = xlBook.Worksheets("Sheet1")

  MsgBox xlSheet.Range("A1").Value

  xlBook.Close False
  xlApp.Quit

  Set xlSheet = Nothing
  Set xlBook = Nothing
  Set xlApp = Nothing
End Sub

Hope this helps! If my answer was helpful, I would greatly appreciate it if you could follow the instructions here so others with the same problem can benefit as well.

Hi Jackie Beedie,

Yes, its possible but no need any code. just import the excel file in access db.

1. browse your excel file
2. select your sheet name
3. tick first rows as header if you needed. otherwise no need to tick.
4. if you want to change the datatype change from there.
5. if you want to add primary key for your table just select let access add primary key.
6. give your table name. by default sheet name will appear.
7. after finish another window will open there if you want tick save import steps. otherwise no need.
8. then click close table will created successfully.