Using Access To store data from Excel with Vba

[swallis]

I download data from a 3rd party, contained in a flat file .dbf format. I need to open each file in the folder, copy the data to a central record set, close the original file and at some point remove multiple instances of any unique records. A unique record consists of a combination of name and date. The following code works (without the delete duplicates) but very quickly runs out of rows in Excel. Which means I need to use Access!! I enjoy playing with Excel and Vba, but have always been daunted by Access. Searching has only confused me more, but I think I’ve worked out how to manually import the first file into a one table database. How do I go about adding to it and removing duplicates (using Vba) and would it be better to remove multiple occurrences as they occur or at the end of importation?

Any help would be appreciated, even if it’s just some clues on search terms. I have Office 2003.

Steve

Sub GetData()
Dim fPATH As String, fNAME As String, NR As Long
Dim wsALL As Worksheet, ws As Worksheet, wbDATA As Workbook
Dim xRow As Long
Dim blnFlag As Boolean
Dim xDirect$, xFname$
Application.ScreenUpdating = False
    ChDir "C:\Price\"
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Price\"
        .Title = "Choose Folder"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
                blnFlag = True
                ActiveCell.Offset(xRow) = xFname$
                xRow = xRow + 1
                xFname$ = Dir
            Loop
        End If
   
        If .SelectedItems.Count <> 0 Then
            fPATH = .SelectedItems(1) & "\"
            fNAME = Dir(fPATH & "*f.dbf")
            Do While Len(fNAME) > 0
            Set wbDATA = Workbooks.Open(fPATH & fNAME)
            Range("A2", Range("ap").End(xlDown)).Select
            Selection.Copy
            Workbooks("Test dbf.xls").Activate
            Sheets("form").Select
            Cells(Rows.Count, "a").End(xlUp).Offset(1).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
              With Selection.Font
                   .Name = "Arial"
                   .Size = 10
              End With
             wbDATA.Close False
            fNAME = Dir
            Loop
        End If
    End With
      
    If blnFlag = False Then MsgBox "No files found"
    
  Application.ScreenUpdating = True
  
End Sub

 

[Joe4]

If you are going to Cross-Post this, please provide the link to the other thread per our rules on Cross-Posting (rule #13 here: Forum Rules).

Here is that link: http://www.mrexcel.com/forum/micros...ata-access-previously-posted-excel-forum.html