Macro to pull in file info to create table

[ragnar12]

I currently have a excel macro (see below) that allows me to pull all the information from all the files in the selected folder (folder dynamic, so current method is preferred).

Option Explicit


Const delim = vbTab  'for TAB delimited text files


Sub ImportSRFiles()
   Dim wb As Workbook
   Dim sFile As String
   Dim inputRow As Long
'===================================================================================
'dialog box name
    Application.FileDialog(msoFileDialogFolderPicker).Title = "SR Files"
'open dialog box
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
'change file box cell location
    fd.InitialFileName = Worksheets("Setup").Range("B1")
    fd.AllowMultiSelect = False
    fd.Show
'====================================================================================
'run the refresh macro below
   RefreshSR
   
   On Error Resume Next
   sFile = Dir(fd.SelectedItems(1) & "\" & "*.txt")
   
   Do Until sFile = ""
      inputRow = Sheets("SR Daily Sales").Range("A" & Rows.Count).End(xlUp).Row + 1
      
'open the text file
'format=6 denotes a text file
      Set wb = Workbooks.Open(Filename:=fd.SelectedItems(1) & "\" & sFile, _
         Format:=6, _
         Delimiter:=delim)
      
'copy and paste
      wb.Sheets(1).Range("A1").CurrentRegion.Copy _
         Destination:=ThisWorkbook.Sheets("SR Daily Sales").Range("A" & inputRow)
      wb.Close SaveChanges:=False
      
'get next text file
      sFile = Dir()
   Loop
   
   Set wb = Nothing

Sheets("Setup").Select


End Sub


Sub RefreshSR()
'delete old sheet and add a new one
   On Error Resume Next
   
   Application.DisplayAlerts = False
      Sheets("SR Daily Sales").Delete
   Application.DisplayAlerts = True
   
    Dim ws As Worksheet
    With ThisWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "SR Daily Sales"
    End With
   
   On Error GoTo 0
End Sub


Sub ImportCMGFiles()
   Dim wb As Workbook
   Dim sFile As String
   Dim inputRow As Long
'===================================================================================
'diaglog box name
    Application.FileDialog(msoFileDialogFolderPicker).Title = "Legacy Files"
'open dialog box
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
'change file box cell location
    fd.InitialFileName = Worksheets("Setup").Range("B1")
    fd.AllowMultiSelect = False
    fd.Show
'====================================================================================
'run the refresh macro below
   RefreshSales
   
   On Error Resume Next
   sFile = Dir(fd.SelectedItems(1) & "\")
   
   Do Until sFile = ""
      inputRow = Sheets("DAILY.SALES").Range("A" & Rows.Count).End(xlUp).Row + 1
      
'open the text file
'format=6 denotes a text file
      Set wb = Workbooks.Open(Filename:=fd.SelectedItems(1) & "\" & sFile, _
         Format:=6, _
         Delimiter:=delim)
      
'copy and paste
      wb.Sheets(1).Range("A1").CurrentRegion.Copy _
         Destination:=ThisWorkbook.Sheets("DAILY.SALES").Range("A" & inputRow)
      wb.Close SaveChanges:=False
      
'get next text file
      sFile = Dir()
   Loop
   
   Set wb = Nothing
    
       
Sheets("Setup").Select
   
End Sub


Sub RefreshCMG()
'delete old sheet and add a new one
   On Error Resume Next
   
   Application.DisplayAlerts = False
      Sheets("DAILY.SALES").Delete
   Application.DisplayAlerts = True
   
    Dim ws As Worksheet
    With ThisWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "DAILY.SALES"
    End With
   
   On Error GoTo 0
End Sub

Right now I'm running the macro and I have the access table linked to the file so that it gets the updates. BUT it would be nice to eliminate the excel file completely and hold everything in access. Basically giving access the capability to get the information from these files and create the table itself. I'm no access guru, I'm making the move from excel out of necessity. The file is getting too big to use due to many other tabs manipulating the data and pulling in more data from other places.

Thanks for any help you guys can lend.

 

[Micron]

Not sure what you're asking for. Help to build a database? Replicate the code so you can use it in Access? Import all the Excel data into Access?

 

[LeslyeE]

Access has the capability to update as long as it is left open based on a time stamp. Is this something like what you are asking for? Otherwise you can build link tables and run macros to update, Build a form to allow users to input the data directly into access without them having to have much knowledge of how to use access or you could use the Import function to pull the entire excel sheet in.

Micron has a point. More clarification on what you truly are asking for is needed to give better advice/ direction.