PDA

View Full Version : [SOLVED] Macro to upload data from a file into a summary file



millwos
06-24-2016, 01:01 AM
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
06-25-2016, 09:48 AM
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
06-25-2016, 11:26 PM
mdmackillop,

That is exactly what I envisaged! Very skillful! :clap:

Thanks very much!

millwos
06-27-2016, 05:30 PM
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
06-28-2016, 05:46 AM
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
06-28-2016, 05:37 PM
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
06-29-2016, 05:26 AM
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
06-29-2016, 03:52 PM
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!