asked on

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!

SiddharthRout

>>> 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

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tracyms

ASKER

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?

SiddharthRout

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

tracyms

ASKER

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