Save manipulated excel sheet with different name (

Topic

New Reply

I use the following code to open a .xls file:

    Dim appExcel As Excel.Application
    Set appExcel = CreateObject("Excel.Application")
    Dim strTheFile As String
    strTheFile = CurrentProject.Path & "template.xls"
       appExcel.Workbooks.Open strTheFile
        appExcel.Visible = True
        Dim strSheetName As String
        strSheetName = "DATA"
        appExcel.Sheets(strSheetName).Select


        With appExcel
            .Range("A1").Select
            .ActiveCell.Value = rstIn!fname & "'s Team"
            .Range("a2").Value = rstIn!PxWx & " 20" & rstIn!Year
            .Range("A6").Select
        End With

Now, how do I save the file with a different name?

Reply | Quote

Replies

Add the following declaration near the beginning of your code:

Dim wbk As Excel.Workbook

and change the line

appExcel.Workbooks.Open strTheFile

to

Set wbk = appExcel.Workbooks.Open(strTheFile)

You can then use a line like this to save the workbook under another name:

wbk.SaveAs CurrentProject.Path & “newname.xls”

Reply | Quote

Thank you so much, Hans.

It worked perfectly.

Reply | Quote