CODEWITHSUNDEEP
excelvbams-access
Dgreco63
Dgreco63

Reputation: 1

Importing specific cells from an Excel file into MS Access

We are currently using a simulation package that outputs data to an Excel spreadsheet. The output cannot be customized to the level that fits the company standards. The good news is that the spreadsheet's format is locked. And the data I need is located in (15) different cells that are scattered all over the spreadsheet. (If they were in a column it'd be easy)

I would like to write a VB app in MS Access that would open the file, look at 15 different cells, and then import the data in these cells to a specific field in a table. From there I an do anything I want with the data. But while I'm fairly confident in my abilities with access, I'm having a hard time coming up with code for do what I want to do

The 15 pieces of data I need reside in the cells BU22, X38, X41, AX38, AX41, BW38, Q49, Q54, Q61, Q69, Q74, BP68, V86, BH81, & BI84

From what I understand I can use the ws.Range method like this:

Dim strSecondValue as String
strSecondValue=ws.Range ("BU22")

Getting that info from the excel cell to the MS Access table is proving to be difficult.

Any help here?

Upvotes: 0

Views: 1787

Answers (2)

ASH
ASH

Reputation: 20302

You can simply do this.

Sub ImportDataFromRange()
' Delete any previous access table, otherwise the next line will add an additional table
DoCmd.DeleteObject acTable, "ExcelRange"
' Import data from Excel using a static range
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelStaticRangeData", "C:\your_path_here\ExcelSample.xls", True, "Sheet1!A1:J20"
End Sub    

Private Sub Command0_Click()
   ImportDataFromRange
End Sub

Upvotes: 0

Slai
Slai

Reputation: 22866

External data sources can queried in few different ways.

SELECT * FROM 
[Sheet1$A1:A1], 
[Sheet1$B2:B2] 
IN 'C:\Book1.xlsx'[Excel 12.0; Hdr=No]

To specify field names:

select A.F1 as A1, B.F1 as B2 from 
[Sheet1$A1:A1] as A, 
[Sheet1$B2:B2] as B
in 'C:\Book1.xlsx'[Excel 12.0; Hdr=No]

https://msdn.microsoft.com/en-us/library/bb177907

Upvotes: 1

Related Questions