Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Append Excel Workbook Data to New Workbook

Posted on 2011-03-20
5
Medium Priority
?
330 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35178350
>>> 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 2000 total points
ID: 35178439
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
 
LVL 1

Author Comment

by:tracyms
ID: 35184660
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
ID: 35186698
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
 
LVL 1

Author Comment

by:tracyms
ID: 35195329
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Screencast - Getting to Know the Pipeline

610 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