CODEWITHSUNDEEP
excelms-accessvba
mediocrecoder
mediocrecoder

Reputation: 3

Aggregating data from Excel into Access

I am just starting on a project that will require me to aggregate data from multiple Excel files into Access tables. The tricky part is that my Excel data will not be in tables, but in a minesweeper like mess (you know, where the mines are randomly placed). Of course it is not totally random...

Each Excel file is essentially a tracking form for a project containing budgeting information and other data. Being a FORM means that it is arranged visually rather than logically. Additionally, the data stretches across 4 separate worksheets.

Does anyone have any suggestions for efficiently getting this data into Access?

Assumptions:
1) Each excel file will only become one row of data in Access.
2) Each excel file will be locked so that data is entered in the exact same cells each time

Based on these assumptions, I was thinking of creating a hidden worksheet with appropriate header values that reference each individual cell, thus creating a consolidated data table. Then from Access I was going to create a macro allowing me to select the desired workbook and import a new record from that hidden table. There is of course plenty of information out there on how to import one or more records from an excel table, so if I have to create a dummy table so be it.

Any alternative methods that I should consider though? Any tricks that might simplify the steps I outlined?

Thanks in advance!

Upvotes: 0

Views: 849

Answers (1)

MikeD
MikeD

Reputation: 8941

This seems to be a rather straight forward task. As you explained you are reading the same cell positions from each file, and hence I would skip the idea of creating hidden sheets in Excel. From an architectual point of view, I agree with LS_Dev - keep it simple! All you need is

  1. a selection mechanism allowing you to mark and open (multiple) Excel files
  2. a routine to open and read through each selected Excel file and pick the data you need
  3. a block of code writing that data into one (or more) Access table(s)

all of the three above can be neatly done in one Access VBA module

ad 1.

Sub XLSProcessMulti()
Dim Idx As Long

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Title = "Pick one or more files"
        .Filters.Add "Worksheets", "*.xl*", 1

        If .Show = -1 Then
            For Idx = 1 To .SelectedItems.Count
                XLSProcessSingle .SelectedItems(Idx)
            Next Idx
        End If

    End With
End Sub

ad 2.

Sub XLSProcessSingle(FileName As String)
' needs creation of a reference to [Microsoft Excel xx.y Object Library]
Dim WB As Excel.Workbook
Dim Idx As Long

    Set WB = Excel.Workbooks.Open(FileName)

    ' dummy code here just to show a bit how the Excel object can be accessed
    Debug.Print WB.Name
    For Idx = 1 To WB.Worksheets.Count
        Debug.Print WB.Worksheets(Idx).Name
        Debug.Print WB.Worksheets(Idx).[A1] ' access a cell by its address enclosed in []
    Next Idx

    ' read sheets, ranges etc. by name or position into local variables
    ' and store them in Access table(s)

    ' X = WB.Worksheets(1).[A1]                  ' by position
    ' Y = WB.Worksheets("MySecondSheet").[B2]    ' by sheet name
    ' Z = WB.Worksheets(3).Range("MyNamedRange") ' single cell named range
    ' etc.


    WB.Close
End Sub

ad 3.

your turn :)

Upvotes: 0

Related Questions