Solved

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

Posted on 2013-02-01
3
213 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
[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
  • 2
3 Comments
 
LVL 36

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 36

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes how to programmatically preset the "Pages per Sheet" option that's available with most printer drivers.   This setting lets you do "n-Up" printing, where two, four, or more pages are printed on each sheet of paper. If your …
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…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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