Visual Basic

what issue are you facing?

>>deletes all its tabs and inserts 4 new sheets

why don't you just create a new workbook?

emoreau: Because the one that it is trying to copy has the macro build in and I need the excel file to delete all its tabs and inserts 4 new sheets(4 new empty sheet).
mansooralia_yahoo Please ready my question again.

something like this:


Option Strict On

Imports Microsoft.Office.Interop

Public Class Form1

    Dim oExcel As Object

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        'Dim misValue As Object = System.Reflection.Missing.Value
        'Dim dtmTest As Date = DateTime.Now
        'dtmTest = DateValue(Now)
        Dim strFileToCopy As String = "C:\temp\TestFiles\test.xlsm"
        Dim strNewCopy As String = "C:\temp\TestFiles\NewTest.xlsm"

        'MessageBox.Show(dtmTest.ToString)
        IO.File.Copy(strFileToCopy, strNewCopy)

        xlApp.Visible = True
        xlApp.DisplayAlerts = False

        'Open the new workbook
        xlWorkBook = xlApp.Workbooks.Open(strNewCopy)

        'NOTE : a workbook must contains at least one worksheet
        For Each objWS As Excel.Worksheet In xlWorkBook.Worksheets
            If xlWorkBook.Worksheets.Count = 1 Then Exit For
            objWS.Delete()
        Next
        xlWorkSheet = CType(xlWorkBook.Worksheets(1), Excel.Worksheet)
        xlWorkSheet.Name = "TO BE DELETED"

        'add new worksheets
        For intI As Integer = 1 To 4
            xlWorkBook.Worksheets.Add()
        Next

        'Delete the last worksheet
        For Each objWS As Excel.Worksheet In xlWorkBook.Worksheets
            If objWS.Name = "TO BE DELETED" Then
                objWS.Delete()
                Exit For
            End If
        Next

        xlApp.Run("Test")

        xlWorkBook.Close(SaveChanges:=True)
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        'releaseObject(xlWorkSheet)

        MsgBox("Excel file created , you can find the file c:\")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

Is there anyway to. Ask the user to rename the name of the copied excel sheet?

sheet or workbook? you can use InputBox for each of them.

but for workbook, you can also use the SaveFileDialog (check http://www.emoreau.com/Entries/Articles/2003/06/Common-Dialogs.aspx)

For saving the worksheet. Also were does the code call the macro in the copied worksheet?

>>For saving the worksheet

you to let the user to give a name to each worksheet (instead of sheet1, sheet2, ...)?

>>Also were does the code call the macro in the copied worksheet?

your macro has to saved into "this workbook" or in a module which will be copied with the workbook. The Macros cannot be attached to worksheets.

>>For saving the worksheet
I mean the copied worksheet that was "C:\temp\TestFiles\NewTest.xlsm". Asking the user to rename that file to their preffered name.

>>Also were does the code call the macro in the copied worksheet?
The macro already exists in the file that is being copied from Dim strFileToCopy As String = "C:\temp\TestFiles\test.xlsm" So, the code needs to run the macro from the copied excel file that is now in Dim strNewCopy As String = "C:\temp\TestFiles\NewTest.xlsm"

thanks,
Paul

for the workBOOK name:


        'Dim strNewCopy As String = "C:\temp\TestFiles\NewTest.xlsm"
        Dim strFileName As String = InputBox("Enter new file name")
        Dim strNewCopy As String = IO.Path.Combine("C:\temp\TestFiles", strFileName) + ".xlsm"

by copying the file, all the worksheets and the macros are also copied. But because you delete the worksheets, if any macro are saved within the worksheets, you also delete these macros. This is why you have to save the macro into "this workbook" or in a module

Calling Macro

http://support.microsoft.com/kb/303871

Thanks guys. I figured out a macro that would insert and delete the sheets. So, can someone please resend that final code that includes the 1. Copying the file from Drive. 2. Asking the user to change its name 3. Openning the excel file 4. Running its macros.

Dim sd As New SaveFileDialog
If sd.ShowDialog = DialogResult.Ok The
   IO.File.Copy(sourcepath, sd.FileName)
End If

Rest is in my comment above.

I already provided all this!

you did emoreau :)
Is htere a way to have a print button to print that file that was named by user by giving the option to either print the whole worksheet or only the current sheet that user is on. Like asking if they want to print the whole document or only the current sheet when they clicked on the print button?

you have to use the printout method:
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.printout(VS.80).aspx
http://microsoft-personal-applications.hostweb.com/TopicMessages/microsoft.public.excel.programming/995620/1/Default.aspx

Thanks but how can I have that printing the excel file that was renamed by the user?

thanks