Solved

Append Excel Workbook Data to New Workbook

Posted on 2011-03-20
5
311 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:tracyms
  • 3
  • 2
5 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>> 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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
Comment Utility
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
 

Author Comment

by:tracyms
Comment Utility
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
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
 

Author Comment

by:tracyms
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This is about my first experience with programming Arduino.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now