Prev: Removing tables while preserving content
Next: VBA To Create Complex Field Code?
From: Doug Robbins - Word MVP on 23 Apr 2010 18:08
You have not opened the workbook.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"Dave Williams" <DaveWilliams(a)discussions.microsoft.com> wrote in message
news:6BC8138C-8A3B-435A-99E6-57AFC244021B(a)microsoft.com...
> Doug:
>
> I really appreciate your recommendations. The one for controlling Excel
> works great and so does the filedialog...
>
> I updated the code utilizing the above and added some code for the meat of
> what I need to do. Problem is my code is not working now... Specifically
> if
> I retrieve data from Excel and attempt to paste into Word it doesn't
> work..
>
> An example of what I mean is the following code:
>
> 'retrieve value of cell D2 in chosen workbook and place in active word
> document at bookmark NovStudyCode_D2
> ActiveDocument.Bookmarks("NovStudyCode_D2").Range = Sheets("Novartis IRT
> Decision Grid").Range("D2").Value
>
> This exact code worked before....
>
> If I go to the immediate window (while pausing the code) and hover over ?
> Range("D2").value I get an error: Method Range of object _ global Failed
>
> Where am I going wrong? Thanks in advance for all your assistance I
> really
> appreciate your direction...:)
>
> My full code is:
>
> Sub CreatecURS()
>
> Dim oXL As Excel.Application
> Dim oWB As Excel.Workbook
> Dim oSheet As Excel.Worksheet
> Dim oRng As Excel.Range
> Dim ExcelWasNotRunning As Boolean
> Dim WorkbookToWorkOn As String
>
> Dim fd As FileDialog
> Dim strWorkbook As String
> Set fd = Application.FileDialog(msoFileDialogFilePicker) 'set object as a
> file dialog
> With fd
> .Title = "Select the Excel Workbook to use."
> .InitialFileName = "*.xls?"
> .AllowMultiSelect = False
> If .Show = -1 Then
> strWorkbook = .SelectedItems(1) 'Store name of workbook selected
> Else
> MsgBox "No WorkBook Selected."
> Exit Sub
> End If
> End With
>
> 'specify the workbook to work on
> WorkbookToWorkOn = strWorkbook 'identify workbook to use for data
>
> '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")
>
> 'retrieve value of cell D2 in chosen workbook and place in active word
> document at bookmark NovStudyCode_D2
> ActiveDocument.Bookmarks("NovStudyCode_D2").Range = Sheets("Novartis IRT
> Decision Grid").Range("D2").Value
>
> 'retrieve value of cell I286 in chosen workbook and place in active word
> document at bookmark SystemSetupType_I286
> ActiveDocument.Bookmarks("SystemSetupType_I286").Range = Sheets("Novartis
> IRT Decision Grid").Range("I286").Value
>
> 'check value of F36 and if yes, update checkbox DistrRegHub_F36 value to
> true
> If Sheets("Novartis IRT Decision Grid").Range("F36").Value = "yes" Then
> ActiveDocument.FormFields("DistrRegHub_F36").CheckBox.Value = True
> End If
> 'check value of F37 and if yes, update checkbox DistrCPO_F37 value to true
> If Sheets("Novartis IRT Decision Grid").Range("F37").Value = "yes" Then
> ActiveDocument.FormFields("DistrCPO_F37").CheckBox.Value = True
> End If
> 'check value of F37 and if yes, update checkbox DistrBoth_F38 value to
> true
> If Sheets("Novartis IRT Decision Grid").Range("F38").Value = "yes" Then
> ActiveDocument.FormFields("DistrBoth_F38").CheckBox.Value = True
> End If
>
> If Err Then
> ExcelWasNotRunning = True
> Set oXL = New Excel.Application
> End If
>
> On Error GoTo Err_Handler
>
> If ExcelWasNotRunning Then
> oXL.Quit
> End If
>
> 'Make sure you release object references.
> Set oRng = Nothing
> Set oSheet = Nothing
> Set oWB = Nothing
> Set oXL = Nothing
>
> 'quit
> Exit Sub
>
> Err_Handler:
> MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
> vbCritical, _
> "Error: " & Err.Number
> If ExcelWasNotRunning Then
> oXL.Quit
> End If
>
> End Sub
> --
> Thanks!
>
> Dave
>
>
> "Doug Robbins - Word MVP" wrote:
>
>> To see how to properly access and then release Excel, see the article
>> "Control Excel from Word” at:
>>
>> http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm
>>
>> To allow the user to browse to and select the workbook, use
>>
>> Dim fd As FileDialog
>> Dim strWorkbook As String
>> Set fd = Application.FileDialog(msoFileDialogFilePicker)
>> With fd
>> .Title = "Select the Excel Workbook to use."
>> .InitialFileName = "*.xls?"
>> .AllowMultiSelect = False
>> If .Show = -1 Then
>> strWorkbook = .SelectedItems(1)
>> Else
>> MsgBox "No WorkBook Selecte."
>> Exit Sub
>> End If
>> End With
>>
>>
>> --
>> Hope this helps.
>>
>> Please reply to the newsgroup unless you wish to avail yourself of my
>> services on a paid consulting basis.
>>
>> Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
>>
>> "Dave Williams" <DaveWilliams(a)discussions.microsoft.com> wrote in message
>> news:2B814204-AB4D-4B81-A04D-10D0EB5DD568(a)microsoft.com...
>> > I too am trying to utilize data in an Excel sheet. My vba runs from
>> > the
>> > Word
>> > doc... I need to open a MS Excel file (using filedialog if possible)
>> > then
>> > retrieve the data from well over 200 cells within the worksheet, then
>> > transfer the information to the Word document. I will be adding text
>> > and/or
>> > updating check boxes using bookmarks to locate the appropriate document
>> > point....
>> >
>> > I have a couple issues....
>> >
>> > First, once I open the xls I can get the data ok and paste into the
>> > Word
>> > doc.... but how do I close the xls (when finished) so that I can open
>> > later
>> > without an error that the file is locked for editing by myself...
>> >
>> > Also I need direction on how to use the filedialog function to allow
>> > users
>> > to use a standard windows type open file dialog to search their pc for
>> > the
>> > appropriate xls to open... do I actually open the file or can I just
>> > reference the file (getting the name from the filedialog)? The
>> > information
>> > is on the same sheet (name is same) no matter what xls the user will
>> > open...
>> >
>> > Anything you can do to get me on the right track would be great!
>> >
>> > Later perhaps you can give me some insight on what might be the best
>> > programming approach since I need to pull so much from the xls...
>> >
>> > Thanks!
>> >
>> > Dave
>> >
>> >
>> > "Fumei2 via OfficeKB.com" wrote:
>> >
>> >> What have you tried so far? What have you looked up/researched? This
>> >> is
>> >> a
>> >> very common thing.
>> >>
>> >> Also, you do not state if you are working from Excel (getting its own
>> >> information and putting that into Word), or from Word (getting
>> >> information
>> >> FROM Excel, and putting it into its own bookmark).
>> >>
>> >> Working FROM Excel, with an existing Word document
>> >>
>> >> Dim appWord As Word.Application
>> >> Dim wrdDoc As Word.Document
>> >>
>> >> Set appWord = CreateObject("Word.Application")
>> >> Set wrdDoc = appWord.Documents.Open _
>> >> (FileName:="c:\zzz\Test\ExistingFile.doc")
>> >>
>> >> wrdDoc.Bookmarks("PutDataThere").Range.Text =
>> >> Worksheets("Sheet1").Range("A1")
>> >> .Value
>> >>
>> >> The above is not full code (by any means) but the method is basically
>> >> the
>> >> way
>> >> you do it.
>> >>
>> >> 1. you make an instance of the application
>> >> 2. you use that instance
>> >>
>> >>
>> >> Kentae wrote:
>> >> >Hi
>> >> >I need a simple way to copy a value /string from a cell i ecxel to a
>> >> >bookmark in a worddocumnet.
>> >> >Tanks in advance for all support
>> >>
>> >> --
>> >> Gerry
>> >>
>> >> Message posted via http://www.officekb.com
>> >>
>> >> .
>> >>
First  |  Prev  | 
Pages: 1 2
Prev: Removing tables while preserving content
Next: VBA To Create Complex Field Code?