Hi, I want to write a VBA macro in Word that open an Excel file, save that Excel file to another file (to another name), and then clear all content of the original Excel file. How to do that? Thanks!

Recommended Answers

All 3 Replies

You should try Google as your friend :)

Try the following -

Sub OpenExcelFile()

    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open("C:\Documents and Settings\MyExcelFolderHere\Import.xls")
    oExcel.Visible = True
    Set oWB = oExcel.Workbooks.Open(sPath)
    'Rest of code

End Sub

To run a macro contained in an excel file, the following -

Sub OpenExcelFile()

    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open("k:\Test.xlsm")
    oExcel.Visible = True
    oWB.Application.Run "Module1.ShowMsg"

End Sub

To save etc, just search for the criteria needed.

When I do this I get the error message -
User-defined type not defined

Do I need to select some add-onClick Here

I have kind of the same problem..anyone can help me?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.