Append Excel Workbook Data to New Workbook

Hello Experts,

About 3 times a week I run a query that saves a .CVS file to my computer that’s always named the same (Contract.CVS). This file has only one record each time its runs ($A$1:$T$1) and has no column headers. Each time I run the query, it’ll replace the Contract.CVS file (overwrites it with the same name) and has a new record in the same row/range. I’d like to keep each record in a separate spreadsheet so I can keep track of the data. The problem is:

- The file changes each time I run it and has only one row of data so I need to append each record (add each record on the next row of my tracking spreadsheet)

- The Contract.CVS file is closed (will need to extract the data from the closed file into my tracking spreadsheet…?)

- Will need a way to alert me of Contract.CVS file is changed/replaced and automatically open tracking spreadsheet and append the data

- In column "D" of the Contract.CVS file there is financial data that I'd like to total using my tracking spreadsheet

Is this possible to do? Thanks!
LVL 1
tracymsAsked:
Who is Participating?
 
SiddharthRoutCommented:
Ok Here is the code which I tested.

Open Notepad and paste the code below.

Dim oExclApp
Dim wb1, wb2
Dim wb2LastRow

Const xlUp = -4162

Set oExclApp = CreateObject("Excel.Application")

'~~> Change path here for Contract.Csv
Set wb1 = oExclApp.Workbooks.Open("C:\Contract.Csv")

'~~> Change Name and path here for Backup.Csv
Set wb2 = oExclApp.Workbooks.Open("C:\Backup.Csv")

oExclApp.Visible = False

'~~> Get the Last Row available for writing in Backup.Csv
wb2LastRow = wb2.Sheets(1).Range("A" & wb2.Sheets(1).Rows.Count).End(xlUp).Row + 1

'~~> Copy the data from Contract.Csv to Backup.Csv
wb1.Sheets(1).Range("A1:T1").Copy wb2.Sheets(1).Range("A" & wb2LastRow)

wb1.Close False
wb2.Close True

Set wb1 = Nothing
Set wb2 = Nothing

oExclApp.Quit

Set oExclApp = Nothing

Open in new window


To save the above file as VBS, follow the below instructions.

1) Click on File save as and in "Save As Type", select "All Files".  (See Snapshot)
2) In the "File Name" textbox, type "Backup.Vbs".  (See Snapshot)
3) Select the relevant Folder and save the file.  (See Snapshot)

Now you can either manually run the vbs file to take the backup or automate the process so that it runs before your query is run.

I have also commented the code so that it is easier to understand the code.

Feel free to ask questions, if you have any.

Sid
Untitled.jpg
0
 
SiddharthRoutCommented:
>>> Is this possible to do? Yes

You can run a small vbscript file before running the query which will take the backup from Contract.CVS and appended it to say Backup.csv?

Sid
0
 
tracymsAuthor Commented:
Thanks SiddharthRout. This seems to be working fine for appending but I still need a way to automate it after I run the query (which puts the Contract.cvs file on my pc). For example, monitor if the Contract.cvs file changes, then the vbs file will run...any ideas?
0
 
SiddharthRoutCommented:
Yes, there are two ways that I can think of at the moment

1) Check the modified date of Contract.cvs OR
2) Match the contents of Contract.cvs with Backup.csv to find duplicates.

Sid
0
 
tracymsAuthor Commented:
Thanks Sid. I should've been more specific. I open a database to run a report from the Contract.cvs file and wanted a way to automatically remind me to log the data before printing the report. I did a little research on opening a file in vbscript, thinking if I run the script before opening the database I wouldn't have to remember to add the data each time I print the report.

Now, I run the script  - which automatically logs the data from Contract.cvs to Backup.xlsx and opens the database so I can print the report. Thanks for all your help!
Dim oExclApp
Dim wb1, wb2
Dim wb2LastRow

Const xlUp = -4162

Set oExclApp = CreateObject("Excel.Application")

'~~> Change path here for Contract.Csv
Set wb1 = oExclApp.Workbooks.Open("C:\Users\tracym\Documents\Spread\Contracts.Csv")

'~~> Change Name and path here for Backup.Csv
Set wb2 = oExclApp.Workbooks.Open("C:\Users\tracym\Documents\Spread\Backup.xlsx")

oExclApp.Visible = False

'~~> Get the Last Row available for writing in Backup.Csv
wb2LastRow = wb2.Sheets(1).Range("A" & wb2.Sheets(1).Rows.Count).End(xlUp).Row + 1

'~~> Copy the data from Contract.Csv to Backup.Csv
wb1.Sheets(1).Range("A1:T1").Copy wb2.Sheets(1).Range("A" & wb2LastRow)

wb1.Close False
wb2.Close True

Set wb1 = Nothing
Set wb2 = Nothing

oExclApp.Quit


Set oExclApp = Nothing

Set SH = CreateObject("WScript.Shell")
SH.Run "database path.accdb"

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.