VB .Net2002 Save Excel Worksheet as another Excel file

[fox12]

I am building an application using VB .Net2002 (later version will be fine if it is required). I want to read an Excel file, and save each worksheet in it as another Excel file. Is there any body who did that before?

It would be appreciated much if you could show me some sample code, or direct me to some reference.

Thanks a ton in advance.

 

[RonRepp]

Fox:

Lots of different ways to do this. I always use early binding, i.e., set a reference to Excel.xx. You can also use late binding with the createobject command.

This example could be used either way.

Public XL AS Excel.Application

Public Sub OpenXL(ByVal Filename As String)
        XL = New Excel.Application
        XL.Visible = True 'This shows the application.  To keep it hidden, use XL.Visible=False
        Try
            XL.Workbooks.Open(Filename)
        Catch
           XL.Workbooks.Open(RTemp) ''RTemp is a variable of a template used for this program.
        End Try

    End Sub

 Public Sub QuitXL()

''Turn off msgboxes for overwriting files.  WARNING:  Using this method will overwrite any existing file
        XL.DisplayAlerts = False
        XL.Quit()
        XL.DisplayAlerts = True
    End Sub

'''This procedure is longer than what you need, but I needed to parse a date that is included with each filename.

 Public Sub SaveRedX()
        Dim sM As String
        Dim sD As String
        Dim sY As String
        Dim Temp As String
        Dim Pos As Integer
        Dim strD As String
        Temp = XL.Range("G5").Value '(This is a date cell)

        sM = Month(CDate(Temp))
        If CInt(sM) < 10 Then
            sM = "0" & sM
        End If
        sD = DateAndTime.Day(CDate(Temp))
        If CInt(sD) < 10 Then
            sD = "0" & sD
        End If
        sY = Year(CDate(Temp))
        sY = Right(sY, 2)

        strD = sY & sM & sD

        XL.Application.DisplayAlerts = False
        XL.ActiveWorkbook.SaveAs(RedDir & "RedX" & strD & "Jeff.xls")''There are many arguments that you can use with SaveAs.  This example shows the minimal requirements.  RedDir is a variable for a directory.
        XL.Application.DisplayAlerts = True

    End Sub

''Zillions of ways to do this.

With XL
  .Range("A1").Value = "Hello" 'Set Values
  .Range("A2").Value = "GoodBye"
End With

''To loop

Dim P as Integer
For i = 1 to 100 ' Get Values
  XL.Range(i,0).Activate ''Move down the rows
  P=XL.Activecell.Value
  Msgbox(P)
Next

If you have more questions, you should be more specific. Most answers can be found simply by using the Macro Recorder to get the bulk of your code.

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.