automation word/excel copy/paste

[SamDeMan]

Hi

I have some code and i don't know why sometimes i get errors, sometimes i can get parts of it to work. the objective: run a report, save it as a word document, then copy it on to the clipboard, and delete the file. then i want to start a new excel spreadsheet, paste my clipboard in it, and save it. then i will add more code which is not written yet.

this is the code:

    Set MyWordInstance = New Word.Application
    MyWordInstance.Documents.Open "C:\tempRpt.rtf"
    With MyWordInstance
        .Selection.WholeStory
        .Selection.Find.ClearFormatting
        .Selection.Find.Replacement.ClearFormatting
        With .Selection.Find
            .Text = "^m"
            .Replacement.Text = ""
            .Forward = True
        End With
        .Selection.Find.Execute Replace:=wdReplaceAll
        .Selection.WholeStory
        .Selection.Copy
    End With
    MyWordInstance.ActiveDocument.Close
    MyWordInstance.Quit
    'Kill "C:\tempPayperiodRpt.rtf"
    
    Set MyExcelInstance = New Excel.Application
    MyExcelInstance.Workbooks.Add
    MyExcelInstance.ActiveWorkbook.SaveAs "C:\PPP"
    MyExcelInstance.ActiveWorkbook.ActiveSheet.Range("B5").Select
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("B5")
    MyExcelInstance.ActiveWorkbook.Save
    MyExcelInstance.ActiveWorkbook.Close
    MyExcelInstance.Quit

 

[SMatthews]

Why don't you just save the report as an Excel file to begin with?

 

[SamDeMan]

you missed the point. this technique will allow me to save a report as is and then have it saved in excel. if you save a report to excel you would get entirely different results. whats saved in excel is a query, not the report. i am just having problems with the vba part of excel. i am new to it.

thanks,

sam

 

[mcirvine]

what error do you get and which part aint working? I tried your excel part and code works fine for me

 

[SamDeMan]

hi

i already got the answer on microsoft discussion boards. basically i need to refrerence it a bit different don't ask me why. it works in excel, but when i automate from access i was having problems.

see the other post here:
http://www.microsoft.com/office/com...misc&mid=9208d844-faef-404b-a82d-bdbc4477b2c2

sam