Best way to collect data from spreadsheets

Sanbiz94

New Member
Joined
Sep 26, 2011
Messages
29
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.


Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top