Opening excel from word

Topic

New Reply

Hi all, this one is tearing my (remaining) hair out. Running the macro opens two sessions of Excel, and pastes the data into both windows. I’m guessing it has something to do with the checking to see if Excel is already open, but I’m still a novice at this. Any ideas on how to make Excel open only one time?

Code:

Sub Exportwordtoexcel(control As IRibbonControl)
    Dim wordDoc As Object
    Dim oXL As Excel.Application
    Dim DocTarget As Word.Document
    Dim Target As Excel.Workbook
    Dim tSheet As Excel.Worksheet
    Dim oAddIn As Excel.AddIn
    
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
    QuestionToMessageBox = “Do you want Excel to open and paste your selection?”
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, “QuikBots for Word”)
    If YesOrNoAnswerToMessageBox = vbYes Then
    
Set wordDoc = GetObject(, “word.application”)
Selection.Copy

‘If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, “Excel.Application”)

If Err Then
    Set oXL = New Excel.Application
    For Each oAddIn In oXL.AddIns
        With oAddIn
            If .Installed Then
                .Installed = False
                .Installed = True
            End If
        End With
    Next oAddIn
End If

oXL.Visible = True

Set Target = oXL.Workbooks.Add
Set tSheet = Target.Sheets(1)
tSheet.Paste
 
    Else
    End If

End Sub

Reply | Quote

Replies

Lets see.

For starters, MsgBox returns an integer so you need to change the Dim statement to
Dim YesOrNoAnswerToMessageBox As Integer

Then you change the few lines of code to
‘If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, “Excel.Application”)
On Error GoTo 0

If oXL Is Nothing Then

Reply | Quote

Andrew,

I just read axbean’s initial post in this thread and your reply with mounting excitement! I’m hoping that the two of you confirmed that I CAN access Excel spreadsheets from VBA code running in Outlook.

I agreed to develop a simple email handler for a service organization of which I am a member. My assumption in taking this on was that I would be able to access Excel spreadsheets from VBA code executed on the arrival of an email in Outlook. I’ve almost got the design to the point where I’m ready to start throwing code. PLEASE tell me I haven’t wasted my time and that axbean’s code is the way I should be going.

Reply | Quote

RiverKing – I don’t know how much time you’ve spent, nor what you’ve already achieved so I have no idea on whether you’ve wasted any time at all.

Yes you can use VBA to have different applications talk to each other. You can drive Outlook from Excel or vice versa. Elements of Axbean’s code could be adapted for your purposes but it’s certainly not the complete solution. You could code this from two directions. Either Outlook appends a record in Excel everytime a mail arrives OR Excel queries Outlook when you ask it for an update on what emails have arrived. It isn’t necessary to always maintain the Excel file in step with the email status if no-one is actually looking at the Excel document.

I’m not sure on your terminology. “Ready to start throwing code” sounds like you could be frustrated and deleting things you’ve already done OR are about to start writing code.

If you do some google searches for keywords such as “VBA Outlook NewMail Event” then you can get some ideas on how to trigger a macro when an email arrives. This information could then be used with elements of the code above to come up with a solution.

Reply | Quote

Thanks for the reply, Andrew; you’ve set my mind at ease. “Ready to start throwing code” is kind of a throwback expression; back in the day on 64K mainframes, we used the expression to throw code as meaning writing code prematurely, before the design had been carefully considered. I still have some design work to do but knowing some details of the code that will be required is a big help.

This simple application (aren’t they all simple?) will forward email from one member of our group to one or more other members without the sender knowing the email address of any of the others. The reason for this is that few members consistently use BCC:, thereby broadcasting all of our email addresses to the world. Then there’s the eternal problem of maintaining multiple email address lists for an organization of more than 100 people with membership changing by about 10% per year.

My plan is to have the application (I’m calling it ‘4:’) run in a Windows/Outlook environment. Since volume is expected to be pretty low (10 emails in a 24-hour period would be a lot), I plan to have the application respond to an Outlook NewMail Event and go from there. Multiple address lists, in an Excel workbook for better security and more simplicity than in Outlook itself, will be maintained manually or, more likely, by a set of only six commands to be used only by one or more designated Administrators.

That’s probably Too Much Information but I thought I’d put it out there if you or any others might want to comment. Any and all suggestions are welcome at this point (after it’s been installed, maybe not :)).

Chuck

Reply | Quote

Chuck

I wouldn’t approach this as a VBA problem without firstly considering other ways to do this.

For instance, I would have thought there are already tried and true methods for distributing emails automatically. This should happen at the server level rather than the client level (since the server is ‘always on’ but the client is only on when logged in and running outlook). You create group email accounts eg staff@company.com and any emails received automatically forward to the members of that group. Your email administrator manages who is in the group and external parties don’t know the membership. This protects the individual accounts from external view and allows you to configure the members of each group whenever you like.

I think you should post your problem to the Outlook forum here and see if someone can suggest how this can be done.

Alternately, you could search through the forum (or wider google search) to learn the key terms. Threads like this mention keywords that I would be doing a search on. http://windowssecrets.com/forums/showthread//137983-Bad-practice-to-allow-external-users-to-send-to-Exchange-distrib-lists?highlight=email+group+distribution+exchange

However, if you have an email administrator – just ask them.

Reply | Quote