CODEWITHSUNDEEP
excelms-accessvba
Daniel Knoesel
Daniel Knoesel

Reputation: 13

Importing specific cell from Excel to MS Access

Hey stackoverflow community,

I'm trying to import a specific cell from a excel sheet to ms access.

I already created in Access a code that puts a specific field from excel in a access table.

Const fName As String = "C:\Users\...\" & varP_ID & ".xlsb"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ImportData", _
        fName, False, "Project Details!H12:H12"

The problem is, that the excel file is very dynamic and changes. Because of this I recorded a macro in excel that finds the specific field in the sheet. 

Dim Field As String
Cells.Find(What:="goal", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
Selection.End(xlDown).Select

Field = Selection.Text

I putted the cell that i want to import to access in the variable "Field". Now I do not know, how to combine these 2 Codes.

How can i get the variable "Field" in access? What i have to code in access, that I import a specific cell from excel, that is in every excel sheet at a different place?

Upvotes: 1

Views: 5876

Answers (2)

ASH
ASH

Reputation: 20302

There are many ways to do this.

Sub ImportDataFromRange()

' Assign the Excel Object
Dim excelapp As Object
Set excelapp = CreateObject("excel.application")

' Assign the workbook
Dim wb As Object
Set wb = excelapp.Workbooks.Open("C:\your_path\Excel.xls")

' Assign the result of your CountA function used in the next line
Dim numberofrows As Integer

' Get the bottom-most row number needed to complete our dynamic range address
numberofrows = 2 + excelapp.Application.CountA(wb.worksheets("Dynamic").Range("A1:A10"))

' Delete any previous access table, otherwise the next line will add an additional table each time it is run
DoCmd.DeleteObject acTable, "ExcelDynamicRangeData"

' Import data from Excel using a range that now knows where bottom row number is
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelDynamicRangeData", "C:\your_path\Excel.xls", True, "Dynamic!A1:B" & numberofrows

' Close and clean
wb.Close
Set wb = Nothing
excelapp.Quit
Set excelapp = Nothing

End Sub    

Private Sub Command0_Click()

ImportDataFromRange

End Sub

Also . . . 

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable 
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0 
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Upvotes: 0

Gustav
Gustav

Reputation: 55831

This can be done much easier.

Create a Named Range holding the cell(s) to read data from.

Then specify that Named Range when calling TransferSpreadsheet.

Edit:

Create Named Range (example):

Range("A1").Select
    Cells.Find(What:="goal", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
ActiveCell.Offset(1,5).Resize(1,60).Select
ActiveWorkbook.Names.Add Name:="Lots", RefersTo:=Selection

Upvotes: 1

Related Questions