MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Can't save/close workbook opened with GetObject using VBA

  Asked By: Anita    Date: Jan 08    Category: MS Office    Views: 1414

I guess I need another good nights sleep because I'm stumped.
Following is the code I'm having problems with. I can't figure out
why it isn't saving and closing the file I opened with GetObject.
I've commented as much detail as I could to help with looking at
this. Thanks for any help you can give.

Sub SaveInfo() 'Changing this to Function does not fix

Dim sMyValue As String
Dim iTimeToWrite As Integer, iCounter As Integer
Dim lRow As Long
Dim wbMyWorkbook As Workbook
Dim wsMySheets(2) As Worksheet

'This worksheet belongs to the file that contains this sub() and is
open at this time.
Set wsMySheets(1) = Worksheets("Data for transfer")

sMyValue = wsMySheets(1).Cells(8, 1)

'The following line does not wrap in excel therefore there isn't a
need for the _ linker thingy. Just displays this way in this post.
Set wbMyWorkbook = GetObject
("\\Server\shareddocs\Receiving\Receiving Trends.xls")

'When the file was saved (saved it manually) nothing was hidden so
don't understand why it opens hidden.
wbMyWorkbook.Windows("Receiving Trends.xls").Visible = True

Set wsMySheets(2) = wbMyWorkbook.Worksheets("Data")

iTimeToWrite = False
wsMySheets(2).Application.Goto ("R1C1")

lRow = 0
lRow = lRow + 1
If wsMySheets(2).Cells(lRow, 1).Value = "" Then iTimeToWrite
= True
If wsMySheets(2).Cells(lRow, 1).Value = sMyValue Then
iTimeToWrite = True
Loop While iTimeToWrite = False

For iCounter = 1 To 24
wsMySheets(2).Cells(lRow, iCounter) = wsMySheets(1).Cells(8,
Next iCounter


'Everything works up to this point. The following two lines do not
execute. Sub() completes with no error messages that come up.

Set wbMyWorkbook = Nothing
Set wsMySheets(1) = Nothing: Set wsMySheets(2) = Nothing
sMyValue = ""
iTimeToWrite = 0: iCounter = 0: lRow = 0

End Sub



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on Can't save/close workbook opened with GetObject using VBA Or get search suggestion and latest updates.