Geek asks and answers

Import dynamic and static ranges from Excel to MS-Access that do not start in cell A1

How do I link a range of data from an Excel spreadsheet so that data in that range displays in a usage table in Access?

Linking to an Excel worksheet that stores data starting from cell A1 is simple enough, but when the data in the Excel spreadsheet starts elsewhere in the worksheet, I'm not sure how to access to specify it, especially if it's a range without A1 - dynamic.

Access does not seem to pick up named / dynamic ranges when going through the import / link wizard.

Real World Scenario:

I have an Excel spreadsheet file, let it " ExcelFile1.xls ", which is provided to me from the outside (so I can not change its formatting).

1 sheets / tabs, let’s call it “Dynamic”, has a range of data that I want to get as a table in Access, but its column headings start at line 14, going to the EL column. What I would like to get for access is to select this data range in the form of a table. In addition, " ExcelFile1.xls " will also be updated periodically, i.e. A new version of the file "ExcelFile.xls" will be available, but with more data below the column headings of row 14, so I would ideally like Access to get new data in this range whenever I overwrite the previous version of " ExcelFile1.xls ".

Excel, " ExcelFile2.xls", .

/, "Static", , Access, " ExcelFile2.xls", , Access. A14:O19 (.. ).

, : Access 2 2 Excel 2 , Access. 1 , . , Access , , Excel .

+5
2

, , , , , Excel , , Excel A1.

. , , , - .

:

, , Excel - , - .

, :

  • C:\Users\Matt\Desktop\ExcelFile1.xls Excel

  • Dynamic , Excel

  • A14:A2000 , , , . : , ; , , ; , , , Excel.

  • ExcelDynamicRangeData , Access, , Excel.

  • Dynamic!A14:EL , / (-) Excel. / , , , numberofrows .

  • numberofrows = 13 ... 13 , , . . 4, 3.

  • Command0 , .

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:\Users\Matt\Desktop\ExcelFile1.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 = 13 + excelapp.Application.CountA(wb.worksheets("Dynamic").Range("A14:A2000"))

' 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:\Users\Matt\Desktop\ExcelFile1.xls", True, "Dynamic!A14:EL" & numberofrows

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

End Sub    

Private Sub Command0_Click()

ImportDataFromRange

End Sub

:

, Excel CountA .

, :

  • C:\Users\Matt\Desktop\ExcelFile2.xls Excel

  • ExcelStaticRangeData , Access, , Excel.

  • Static!A14:EL20 Excel, . , Excel Excel, , .

  • Command0 , .

Sub ImportDataFromRange()

' Delete any previous access table, otherwise the next line will add an additional table
DoCmd.DeleteObject acTable, "ExcelStaticRangeData"

' Import data from Excel using a static range
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelStaticRangeData", "C:\Users\Matt\Desktop\ExcelFile2.xls", True, "Static!A14:EL20"

End Sub    

Private Sub Command0_Click()

ImportDataFromRange

End Sub

NB:

  • acSpreadsheetTypeExcel9 Excel; Excel 2000; Excel, this, , ;.xlsx , acSpreadsheetTypeExcel12Xml.

  • Access . , Access , , . Swap-out acLink acImport, .

  • Access ( , ), , DoCmd.DeleteObject acTable, "yourAccessTable" .

, - , CountA , .

@david-zemens, @gord-thompson, StackoverFlow , - , , .

+4

Access, , .

1 /, "", , Access, 14, EL. , , - . , "ExcelFile1.xls" , "ExcelFile.xls", , 14 , , Access , "ExcelFile1.xls".

, Named Range. , / , .

, , :

http://support.microsoft.com/kb/830287

VBA , /, Access. , Access, .

/, "Static", Access, "ExcelFile2.xls", Access, . A14: O19 (.. ).

, =$A$14:$O$19, . , .

EDIT Excel, , , //etc Access.

Sub ImportDataFromRange()
'Access variables
Dim dbFile As Database
Dim tbl As TableDef, fld As Field

'Excel variables
Dim xlApp As Excel.Application
Dim xlFile As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim r#, c#
Dim clVal As String 'string to hold cell value, may need to modify this type.

Set dbFile = CurrentDb

'Use this to create a new table definition
'    Set tbl = dbFile.CreateTableDef("Test")
'Use this if your table already exists:
    Set tbl = dbFile.TableDefs("Test")

'Get the info from Excel:
Set xlApp = New Excel.Application

Set xlFile = xlApp.Workbooks.Open("C:\Users\david_zemens\desktop\Book1.xlsx")
Set xlSheet = xlFile.Sheets("Sheet1")
Set xlRange = xlSheet.Range("A1:B10")

    For r = 1 To xlRange.Rows.Count
        For c = 1 To xlRange.Columns.Count

            'Add code to append new fields/records/etc to your table

        Next c
    Next r

xlApp.Quit
Set xlApp = Nothing


End Sub

, , !

+2

More articles:


All Articles