Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-07-2019, 06:30 AM
MGW1138 MGW1138 is offline Windows 10 Office 2016
Novice
 
Join Date: Feb 2019
Posts: 2
MGW1138 is on a distinguished road
Smile How to access Excel from Word

I want to scan a system for open Excel workbooks.

Sub GetExcelData()
Dim WB As Workbook
For Each WB In Application.Workbook
If MsgBox("Do you want " & WB.Name, vbYesNo) = vbYes Then
MsgBox ("Yes")
End If
Next WB
End Sub

The compiler complain and thinks that workbook is a user defined type.
I do have the "Microsoft Office 16.0 Object Library" checked.

What am I missing.

Thanking you in advance (I have done Excel VBA for years but just starting Word)
Reply With Quote
  #2  
Old 02-07-2019, 01:57 PM
Guessed's Avatar
Guessed Guessed is offline Windows 10 Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,118
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

A Workbook is an object that requires the Microsoft Excel Object Library. Try ticking that one instead. However it will still have a problem because Application.Workbook in this instance is going to be Microsoft Word and it doesn't contain Workbooks.

Do a google search for "VBA GetObject application excel" if you are trying to access open workbooks from Word.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 02-07-2019, 02:18 PM
Charles Kenyon Charles Kenyon is online now Windows 10 Office 2016
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 5,773
Charles Kenyon is a name known to allCharles Kenyon is a name known to allCharles Kenyon is a name known to allCharles Kenyon is a name known to allCharles Kenyon is a name known to allCharles Kenyon is a name known to all
Default

Also, the Workbooks collection only deals with workbooks which are open in Excel. Is this what you want?


Otherwise, you would be searching the file system for .xlsx files (and xlsm and xls files?).
Reply With Quote
Reply

Tags
excel to word

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel or Access hollies Excel 17 07-18-2017 03:21 AM
Excel or Access for DB? John H. Power Word 3 08-03-2016 11:52 AM
How to access Excel worksheet data from Word jules43 Word VBA 1 03-23-2016 02:36 AM
Moving addresses from a letter in word into access or excel docwilll Office 3 02-22-2014 03:40 PM
Excel to Access to Excel for report automation Nicholaspoe Excel Programming 10 10-08-2013 08:13 PM


All times are GMT -7. The time now is 07:23 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft