Thread: CONVERT Excel Macro to Access?

Hello,

Can someone please help me convert this macro that was initially written for Excel into a functioning Access macro? I would still like to pull data from the source excel workbooks, but simply make an access database the destination.

Thank you

Sub datacollect()
  
Dim C As Long
  Dim DstWks1 As Worksheet
  Dim LastCol As Variant
  Dim LastRow As Long
  Dim R As Long
  Dim SrcWkb As Workbook
  Dim StartRow As Long
  Dim wkbname As Variant
  Dim xlsFiles As Variant
  
   'Starting column and row for the destination workbook
    C = 2
    R = 2
   'Set references to destination workbook worksheet objects
    Set DstWks1 = ThisWorkbook.Worksheets("Data")
    
   'Starting row on source worksheet
    StartRow = 1
    
   'Get the workbooks to open
    xlsFiles = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
    Application.AskToUpdateLinks = False
      If VarType(xlsFiles) = vbBoolean Then Exit Sub
      
     'Loop through each workbook and copy the data to this workbook
      For Each wkbname In xlsFiles
        Set SrcWkb = Workbooks.Open(Filename:=wkbname, ReadOnly:=True)
          Set LastCol = SrcWkb.Worksheets("Equity").Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False)
          If Not LastCol Is Nothing Then LastCol = LastCol.Column Else
          LastRow = SrcWkb.Worksheets("Equity").Cells(Rows.Count, LastCol).End(xlUp).Row
            If LastRow >= StartRow Then
              With SrcWkb.Worksheets("Equity")
                DstWks1.Cells(R, C).Resize(LastRow - StartRow + 1, 1).Value = _
                .Range(.Cells(StartRow, LastCol), .Cells(LastRow, LastCol)).Value
              End With
      'Pulls the pertrac ID number located on the first page of each template
            With SrcWkb.Worksheets("Data Entry")
                DstWks1.Cells(1, C).Value = _
                  .Cells(1, 13).Value
              End With
            End If
      
     
      
        C = C + 1
Continue:
        SrcWkb.Close savechanges:=False
      Next wkbname

End Sub

Hi, could you post an example of one of the source data workbooks?