Macro to upload data from a file into a summary file

[millwos]

Hi,

I'm trying to create a macro that will upload data from a timesheet into a new row in a summary file.

The idea is for each employee to upload their hours into the summary file using their timesheet file.

Does anyone if this is possible?

Any help is much appreciated, thankyou.

[mdmackillop]

This will write your data to a csv file. The Summary will display the data from this file.
The sample files should be extracted to C:\VBAX

.
'Timesheet
Sub Upload()
    Open "C:\vbax\data.csv" For Append As #1
    Print #1, Join((Application.Transpose(Range("Data"))), ", ")
    Close #1
End Sub



'Summary
Sub Update()
    Application.ScreenUpdating = False
    Range("D5").QueryTable.Refresh BackgroundQuery:=False
    Columns("A:P").EntireColumn.ColumnWidth = 13
    Columns("D:E").EntireColumn.ColumnWidth = 0
    Application.ScreenUpdating = True
End Sub

[millwos]

mdmackillop,

That is exactly what I envisaged! Very skillful!

Thanks very much!

[millwos]

I've moved the files to a different directory, and have successfully updated the timesheet macro to record to the new directory where the csv is, however the summary file isn't updating now.

What do I change in the summary macro to find where the csv file has moved too?

Thanks again for your help.

[mdmackillop]

In the Summary spreadsheet, delete the table below the headers, Select D5, on the Data tab select "From Text" and browse to the CSV file. Right click D5 and select "Data Range Properties" Clear "Prompt for File Name on Refresh". The macro does not need to be changed.

[millwos]

Thanks, I just have a few more questions regarding the "From Text" Data import.
When you click the "From text" button and browse to the csv file, the text import wizard appears, what settings do you apply in here? i.e. Delimited, tab, general? Then an import data screen appears asking where do you want to put the data? Existing worksheet: $D$5 . And a check box "Add this data to the data model" is down the bottom.
If I click ok, all the data from the csv appears in cell $D$5 in amongst commas.
Im using Excel 2016.

[mdmackillop]

You should really be trying these options to learn the process. That's how we all started.
Delimited: Comma separator - clear other boxes
Column 1 date = DMY
Existing worksheet: $D$5

[millwos]

Yes, I had been trying hard. Have now found where the error was. I used $A$4 instead of $D$5 and it now works.

Thanks again! You have taught me very well! Much appreciated!