Help opening an Excel file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

erick-flores - 26 Sep 2006 16:45 GMT

Hello all

I need to open an excel file from my access form. Everything is working
fine, here is the code that I am using:

Dim MyObject As Object
Set MyObject = GetObject("C:\MIS Inventory.xls")
MyObject.Application.Visible = True
MyObject.Parent.Windows(1).Visible = True

The problem is, I want to open a file that is on the network, this is
the path:
\\Dc-tfh\mis\MIS Misc\Inventory\Hardware Inventory.xls

It is working fine when the file is on my C: drive but not the one in
the network. Any ideas why? do I need to add something to my code?

Also I would like to open an specific worksheet, how do I do that?
I have been reading in this NG and try different codes w/o any luck.

Finally, Let say I have 100 records in the excel file...I am using
Access to navigate through all those records, I find a mistake (or want
to update) in the record number 45, is there a way to click an open the
excel file (that has all the records) and automatically place the
cursor in the record 45? so I can easily change it w/o having to look
through the entire file

Thank you in advance

Reply to this Message

erick-flores - 26 Sep 2006 17:03 GMT

> Dim MyObject As Object
> Set MyObject = GetObject("C:\MIS Inventory.xls")
[quoted text clipped - 4 lines]
> the path:
> \\Dc-tfh\mis\MIS Misc\Inventory\Hardware Inventory.xls

OK, so I moved the file one folder up and it worked...right now looks
like:
\\Dc-tfh\mis\MIS Misc\Hardware Inventory.xls

...I wonder why it wont open the other way, any ideas? ( I still need
to use the first path)

Reply to this Message

Klatuu - 26 Sep 2006 18:52 GMT

The level of the folder makes no difference. Either you don't have the name
correct or you don't have permissions to the folder. What error are you
getting when you try do open it in the lower folder?

Here is how you would go to a specific cell on a specific sheet (copied from
VBA Help)

Worksheets("Sheet1").Activate
Range("A1:B3").Select

Now, getting to record 45. If you mean record 45 in your Access table, you
can't be sure what record 45 is. There is no assurance that the Access
recordset will be in the same order as the worksheet. Record numbers in
Access are not static. They are simply a relative position in the current
recordset. Chaning the sort or filter of a recordset changes all the record
numbers.

You probably need to determine fields in both objects to match up on and
position on the correct cell in the worksheet.

> > Dim MyObject As Object
> > Set MyObject = GetObject("C:\MIS Inventory.xls")
[quoted text clipped - 11 lines]
> ....I wonder why it wont open the other way, any ideas? ( I still need
> to use the first path)

Reply to this Message

erick-flores - 26 Sep 2006 20:34 GMT

Thanks for ur answer, this is the error I am getting:

*Cannot access 'Hardware Inventory.xls'.
Than I click "OK" and the next error appears:
*Automation error
Unspecified error

I checked the name of the path and its spell correctly.
I though about the permission but I do have permission (full rights) to
that folder, so I dont think thats the problem

Any ideas?

Reply to this Message

Klatuu - 26 Sep 2006 20:49 GMT

I think this is a subtle problem you may not be aware of. Automation is
tricky. You have to do it exactly right, or you will get weird problems like
this. In this case, the GetObject will throw an error if there is not an
instance of Excel already running. Here is some sample code that may clear
this up for you:

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlApp = CreateObject("excel.application")
Else
End If
Err.Clear ' Clear Err object in case error occurred.
'Set Error Trapping back on
On Error GoTo CreateWorkbook_Error
Set xlBook = xlApp.Workbooks.Open(strGetFileName, 0, True)

> Thanks for ur answer, this is the error I am getting:
>
[quoted text clipped - 8 lines]
>
> Any ideas?

Reply to this Message

erick-flores - 26 Sep 2006 21:11 GMT

It still giving me errors...I am just going to move all the file to the
folder that is working. That will solve all my problem...for now

Thanks for your help :)

Reply to this Message

erick-flores - 27 Sep 2006 17:16 GMT

Hello there...everything was working fine yesterday but now the errors
are back. Even in the folder that was working good. Only one form out
of 5 will open the Excel file, its giving me the same errors. Do you
have any ideas why this may happend? It is driving me crazy

Thank you

Reply to this Message

erick-flores - 27 Sep 2006 17:19 GMT

This is the code I am using:

Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim MyObject As Object
Set MyObject = GetObject("\\Dc-tfh\mis\MIS Misc\Hardware
Inventory.xls")
MyObject.Application.Visible = True
MyObject.Worksheets("Computer Information").Activate
MyObject.Parent.Windows(1).Visible = True

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub

Do I need to add something, where?

Again, this code is working in only one form out of 5.

Reply to this Message