Best way to collect data from spreadsheets

[Sanbiz94]

I have about 15-20 employees I do payroll for each week and I need to manually calculate the 401K benefits i intend to pay each week. Now, I use a very simple spreadsheet (this spreadsheet was already in use before I worked here) to calculate the benefits and each week I resave the sheet under the week ending date. For example - WE 01.03.2024. However I have nothing that automatically keeps track for the data that I put in. I would like a spreadsheet that will automatically "import/copy" the date for one spreadsheet and put it in another one. Is that something I can do? Keep in mind that the spreadsheet name will change each week. I hope this makes sense

 

[ranman256]

paste code into a module in your Main workbook.
(or paste in PRIVATE WORKBOOK module so it can run in any workbook)

run OpenFile4Processing to pick the file you want to use,
it will load it into your main app.

Sub OpenFile4Processing()
' open file to load their data sheet
Dim vFile
Const kStartDIR =  Environ$("USERPROFILE") & "\Documents\"
Dim wbMain As Workbook, wbSrc As Workbook

On Error GoTo EndItAll

Set wbMain = ActiveWorkbook
vFile = UserPick1File(kStartDIR)
If vFile = "" Then Exit Sub


Range("A1").Select
    Workbooks.Open Filename:=vFile
    Set wbSrc = ActiveWorkbook
    
    wbSrc.Sheets(1).Select
    wbSrc.Sheets(1).Copy Before:=wbMain.Sheets(1)
    wbSrc.Close False
    
    wbMain.Save
    
EndItAll:
Set wbSrc = Nothing
Set wbMain = Nothing
End Sub


private Function UserPick1File(Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialogMsg As String, sDecr  As String, sExt As String
Const msoFileDialogViewList = 1
Const msoFileDialogSaveAs = 2
Const msoFileDialogFilePicker = 3
Dim lFilterIndex As Long

'getFilterTxt pvFilter, sDecr, sExt, sDialog
If IsMissing(pvPath) Then pvPath = "c:\"

''SetFileFilter pvFilter, sDecr, sExt, sDialogMsg

'Application.FileDialog(msoFileDialogSaveAs) =2     'SAVE AS
'Application.FileDialog(msoFileDialogFilePicker) =3  'file OPEN

With Application.FileDialog(3)   'REFERENCE not needed now : Microsoft Office XX.0 Object Library
    .AllowMultiSelect = True
    .Title = sDialogMsg   ' "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
      '.Filters.Add sDecr, sExt
        '.Filters.Add "Access Files", "*.accdb;*.mdb"
     .Filters.Add "Excel Files", "*.xlsx"
        '.Filters.Add "_All Files", "*.*"
        '.Filters.Add "Text Files", "*.txt"
    
      For lFilterIndex = 1 To .Filters.Count
        'Debug.Print lFilterIndex, .Filters(lFilterIndex).Description
        
              'get pdf format from type filter
           If InStr(.Filters(lFilterIndex).Description, "PDF") > 0 Then
               .FilterIndex = lFilterIndex
               Exit For
           End If
       Next
    
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail

        If .Show = 0 Then
           'There is a problem
           Exit Function
        End If

    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
End Function