Solved

Copying contents of an Excel Spreadsheet into another Excel Spreadsheet using VB.NET

Posted on 2013-02-01
3
209 Views
Last Modified: 2013-05-08
We have VB code that is generating a spreadsheet with one data sheet every week with data only no formulas.  Each week there is a new spreadsheet generated with the date and time stamp added to the file name.  

We need to have a "Master" Spreadsheet that contains the contents of all spreadsheets data so we can use the data and create pivot tables.
26012013-124310-tmsht-compliance.xls
01022013-124310-tmsht-compliance.xls
MASTER-tmsht-compliance.xls
0
Comment
Question by:cnrlzen
  • 2
3 Comments
 
LVL 35

Expert Comment

by:Miguel Oz
ID: 38846448
Here is an alternative: (Far more quicker than Excel automation in VB.NET)
- Your VB code to generate a csv file. (not xls)
Then you program (I do not if you need console app or win form) should
1. Merge the csv file contents
2. Save the merge contents to a master xls file.
Let me know if you are happy with the alternative above to provide code for 1 and 2.

Note: Please add VB.NET and Excel zones to question.
0
 

Author Comment

by:cnrlzen
ID: 38852141
Yes that would work.
Please provide code.

Thank you
0
 
LVL 35

Accepted Solution

by:
Miguel Oz earned 500 total points
ID: 38870925
Here is the code to join the csv files
Private Sub JoinCsvFiles(ByVal csvFileNames As String(), ByVal outputFileName As String)
        Debug.Assert(csvFileNames.Length > 0, "Invalid usage")
        Dim sb As New System.Text.StringBuilder()
        Dim columnHeadersRead As Boolean = False
        For Each csvFileName As String In csvFileNames
            Dim tr As System.IO.StreamReader = New System.IO.StreamReader(csvFileName)
            ' Skip appending column headers(the first 3 lines) if already appended
            If columnHeadersRead Then
                tr.ReadLine()
                tr.ReadLine()
                tr.ReadLine()
            Else
                Dim columnHeaders As String = tr.ReadLine()
                sb.AppendLine(columnHeaders)
                tr.ReadLine()
                tr.ReadLine()
                columnHeadersRead = True
            End If
            sb.AppendLine(tr.ReadToEnd())
        Next
        System.IO.File.WriteAllText(outputFileName, sb.ToString())
    End Sub

Open in new window

Before I proceed with  to convert the result csv file to Excel. i need to know if you are using this code in a winform application or what kind of app.
Note: If you are just using for data purposes, the user can convert it to Excel when building the pivot table. The  save to EXcel in C# will look like:
http://stackoverflow.com/questions/4781478/convert-csv-into-xls
but is is easy to translate to VB.NET
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

Suggested Solutions

If you have ever found yourself doing a repetitive action with the mouse and keyboard, and if you have even a little programming experience, there is a good chance that you can use a text editor to whip together a sort of macro to automate the proce…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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