?
Solved

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

Posted on 2013-02-01
3
Medium Priority
?
224 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 2000 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

As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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