Call to open specific named Excel file opens "Personal.xls" file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cjmillerFL - 25 Aug 2006 21:17 GMT

Hello,

I have a VBA that exports the data from a Word table into a Excel file. The
Visual Basic file that is run calls out the specific Excel (in this case
WISTL.xls") file to be opened and then the data imported into the Excel
spreadsheet. The issue I have is that my "Personal.xls" file opens and note
the WISTL.xls file (as I see called out in the code). Please see code below
(since I'm not a guru in Visual Basic and am having a difficult time
explaining this).

Any help would be appreciated. Thanks...

------------------------------------------------------------------------------------------------
Sub UpdtWatchItem()
Dim MyXL As Object
' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean
' Flag for final release.
On Error Resume Next
' Defer error trapping.

Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
' Clear Err object in case error occurred.
Set MyXL = GetObject("D:\Documents and Settings\millecj\Desktop\WIForm
Test\WISTL.xls")
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
MyXL.Application.ScreenUpdating = True
MyXL.activesheet.Range("A3").Select
Dim i As Integer
i = 3
For i = 3 To 200
If MyXL.activesheet.Range("a" & i) = "WI-" &
ActiveDocument.Bookmarks("WIyear").Range _
& "-" & ActiveDocument.Bookmarks("WInbr").Range Then
'If MyXL.activesheet.Range("a" & i) =
ActiveDocument.Bookmarks("WInbr").Range Then
GoTo Continue
End If
Next

Signature

C.J. Miller

Reply to this Message

Jezebel - 25 Aug 2006 23:10 GMT

I think you'll find that Excel is opening both workbooks. If you have a
personal.xls workbook it will open everytime Excel starts. The trick is to
get a reference to the workbook you're interested in.

Dim xlApp as object
Dim xlBook as object

set xlApp = GetObject(,"Excel.Application")
set xlBook = xlApp.Workbooks.Open("D:\..\WISTL.xls")
:
xlBook.Sheets(1).Cell(1,5) = ...

[I can't for the life of me make out what the rest of your code is trying to
do ...]

> Hello,
>
[quoted text clipped - 42 lines]
> End If
> Next

Reply to this Message

cjmillerFL - 28 Aug 2006 19:43 GMT

I'll give it a shot.

I did not write the code, but the code "supposedly" moves data from a Word
form to an Excel spreadsheet. I did not post the whole code, would that be
more helpful if I did that?

Thanks for your time!

Chris

Signature

C.J. Miller

> I think you'll find that Excel is opening both workbooks. If you have a
> personal.xls workbook it will open everytime Excel starts. The trick is to
[quoted text clipped - 57 lines]
> > End If
> > Next

Reply to this Message