Solved

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

Posted on 2013-02-01
3
199 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
Comment Utility
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
Comment Utility
Yes that would work.
Please provide code.

Thank you
0
 
LVL 35

Accepted Solution

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

zlib is a free compression library (a DLL) on which the popular gzip utility is built.  In this article, we'll see how to use the zlib functions to compress and decompress data in memory; that is, without needing to use a temporary file.  We'll be c…
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…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

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

18 Experts available now in Live!

Get 1:1 Help Now