switching between excel and word more than once 2

[jaminewok]

sorry for the repeated posting, but my question is not being answered. i know how to open new and existing documents and workbooks from excel and word.

what i need to know is how to switch between an already open workbook and an already open document to copy and paste things more than once.

i need the vba code for:
alt+tab (from word to excel)
*highlight*
ctrl+c
alt+tab (from excel to word)

thanks

 

[SkipVought]

Hi,

Set an object for each one

set wbk1 = Excel.Workbooks.Open(YourWorkbook)
set doc1 = Word.Documents.Open(YourDocument)

wbk1.worksheets(whatever).range(whatrange).copy
doc1.range.PasteAndFormat (wdPasteDefault)

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[jaminewok]

skip,

here is a segment of my code

Selection.PasteExcelTable False, False, False

Dim wbk1 As Object, a
Dim doc1 As Object, b
Set wbk1 = Excel.Workbooks.Open(YBook1.xls)
Set doc1 = Word.Documents.Open(Yinvestmentpolicysummary.doc)

wbk1.worksheets(Sheet1).range(Chart 2).Copy
doc1.range.PasteAndFormat (wdChartPicture)

End Sub

what am i doing wrong

Thanks.

 

[cLFlaVA]

[tt]wbk1.worksheets(Sheet1).range(Chart 2).Copy[/tt]

should no doubt be

[tt]wbk1.worksheets("Sheet1").range("Chart 2").Copy[/tt]

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[SkipVought]

What is
[tt]
wbk1.worksheets(Sheet1).range(Chart 2).Copy
[/tt]
Chart 2? If it's a CHART, it is NOT a Range, it's an OBJECT in Worksheets("Sheet1"). If so then

wbk1.worksheets("Sheet1").Shapes("Chart 2").Copy

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[jaminewok]

skip and clflava,

heres my code again:

Dim Wbk1 As Object, a
Dim Doc1 As Object, b
Set Wbk1 = Excel.Workbooks.Open("Y:\Book1.xls")
Set Doc1 = Word.Documents.Open("Y:\investmentpolicysummary.doc")

Wbk1.worksheets("Sheet1").Shapes("Chart 2").Copy
Doc1.range.PasteAndFormat (wdChartPicture)

End Sub
--------------------------------

its actually this line thats causing the debugger to pop up:

Set Wbk1 = Excel.Workbooks.Open("Y:\Book1.xls")

thanks.

 

[cLFlaVA]

Works for me.

1. What error are you getting?
2. What application is this written in (word? excel?)

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[SkipVought]

What application are you running this from?

Do you have a reference set to the OTHER application's Object Library in the VB Editor (Tools/References)?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[jaminewok]

clflava, im getting an "Object Required" error.

clflava and skip, this is a word macro. i used excel to automatically open word and run a word macro to paste a table copied from excel. I need to automate going back to excel to copy more tables/charts and paste in word again.

skip, how would i do that. (the OTHER application would be excel)

Thanks!

 

[SkipVought]

in the VB Editor (Tools/References) then scroll down to the Microsoft Excel i.j Object Labrary and Check.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[PHV]

Try to add this before opening the worksheet:
Dim Excel As Object
Set Excel = CreateObject("Excel.Application")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jaminewok]

this program SORT OF works, but i need to switch between already open instances of word and excel, not open new instances.

Dim Wbk1 As Object, w
Dim Doc1 As Object, q
Set objWbk1 = Excel.Workbooks.Open("Y:\Book1.xls")
Set objDoc1 = Word.Documents.Open
objWbk1.worksheets("Sheet1").Shapes("Chart 2").Copy
objDoc1.Shape.PasteAndFormat (wdChartPicture)

perhaps a description of my project can help:
i made a form in access where the user enters in 5 pieces of data. the access module opens an existing excel workbook, transfers the data there, and runs an excel macro. the excel macro makes calculations and charts with the data, then opens an existing word document and runs a word macro. im trying to get the word macro to paste a table onto word that i already copied, then go back to the already open excel workbook, copy another table/chart, and paste it back into word, and etc.

thanks for the help.

 

[PHV]

Take a look at the GetObject function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cLFlaVA]

Here's a function I wrote once that will first see if a Workbook is open. If it is, it will return the Workbook, otherwise, it will open it.

Public Function getWorkbook(szFilename As String, Optional szLocation As String) As Workbook
    On Error GoTo HandleError
    
    If szFilename = "" Then
        Set getWorkbook = Nothing
        Exit Function
    End If
    
    If szLocation = "" Then
        szLocation = ThisWorkbook.Path
    End If
    
    ChDir szLocation
    Set getWorkbook = Workbooks.Open(szFilename, 0, True)
    getWorkbook.Windows(1).Visible = False
    Exit Function
    
HandleError:
    MsgBox Err.Number & " - " & Err.Description
    End
End Function

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[jaminewok]

cLFlaVa,

sorry for this base question, but where do i put the real file name/location? is it szFilename/szLocation?

thanks.

 

[cLFlaVA]

Yeah. szFileName would be something like "workbookname.xls" and szLocation would be something like "..\DirectoryName".

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[jaminewok]

Public Function getWorkbook("Book1.xls" As String, Optional "Y:\Book1.xls" As String) As Workbook

how would i get that to work? because it says expected:identifier when i try it

thanks

 

[cLFlaVA]

Damn, sorry, I left some stuff out.

1) This function should be called as follows:

If wkbk Is Nothing Then
    Set wkbk = getWorkbook()
End If

If you're always going to be opening the same file, then you can alter the function as follows:

Public Function getWorkbook() As Workbook
    On Error GoTo HandleError

    Dim strFile as String
    strFile = "Y:\Book1.xls"

    Set getWorkbook = Workbooks.Open(strFile, 0, True)
    getWorkbook.Windows(1).Visible = False
    Exit Function
    
HandleError:
    MsgBox Err.Number & " - " & Err.Description
    End
End Function

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[jaminewok]

cLFlaVa,
i am always opening the same file, but the program you wrote for that tries to open the already open workbook. also, the programs you wrote are functions, and i have no idea how to autorun a word function from excel. all i know is how to autorun macros:

.ActiveDocument.RunAutoMacros xlAutoOpen
.ActiveDocument.RunMacro1 xlAutoOpen
h = .Run("Macro1")

Thanks.

 

[cLFlaVA]

Right, that's the idea. In my last post, the first segment of code will test to see if the workbook object has been set. If it hasn't, it calls the function. Then, the function will open the workbook, and return the workbook object.

Functions can exist in any module you'd like, but would be most efficient in the most localized module you can use. In other words, place the function in the module that is calling the function, not a globalized module.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!