Solved

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

Posted on 2013-02-01
3
211 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need example 5 128
Need to create an object factory 2 49
Post a good COM tutorial 1 66
What .NET website keeps me current? 9 80
This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

763 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