We help IT Professionals succeed at work.

Visual Basic

It is been about 2 years that I haven't programmed with VB so please be clear in your comments. I am trying to add a button on my form that would copy an excel file from a share drive and deletes all its tabs and inserts 4 new sheets. Like sheet1, sheet2, sheet3 and sheet4. Then it asks the user to name that file. Then it saves that copy into the desktop.
Also, the code needs to open that saved file and runs one of its macros.
I tried to do as much as I could but since this is urgent I couldn't research more about this:
Imports Excel = Microsoft.Office.Interop.Excel
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 Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim dtmTest As Date
        dtmTest = DateValue(Now)
        Dim FileToCopy As String
        Dim NewCopy As String

        MessageBox.Show(dtmTest)
        FileToCopy = "C:\Users\Owner\Documents\test.txt"
        NewCopy = "C:\Users\Owner\Documents\NewTest.txt"
        xlWorkBook.Close()
        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

Open in new window



Thank you all for your help,
Paul
Comment
Watch Question

what issue are you facing?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>deletes all its tabs and inserts 4 new sheets

why don't you just create a new workbook?

Author

Commented:
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.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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

Author

Commented:
Is there anyway to. Ask the user to rename the name of the copied excel sheet?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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)

Author

Commented:
For saving the worksheet. Also were does the code call the macro in the copied worksheet?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>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.

Author

Commented:
>>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
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
I already provided all this!

Author

Commented:
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?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:

Author

Commented:
Thanks but how can I have that printing the excel file that was renamed by the user?
Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
call the Printout method just before closing the workbook

Author

Commented:
thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.