[AccessD] Link to spreadsheet

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



I would also change

Dim varFileName As Variant

to 

Dim strFileName as String

Variant may also cause you problems as VBA will 'best guess' what it should be.  It can guess wrong.

Regards
Darryl. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Tuesday, 21 September 2010 5:07 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Link to spreadsheet

I am using the following code in an attempt to link to a password protected spreadsheet. I am running it from a terminal server instance of Access 2007 if that matters. I get the following error message. Could it have anything to do with the spreadsheet asking if I want to enable macros when I open it manually? 

The file '-1' does not exist

Here is the code:

Sub LinkSpreadsheet()

On Error GoTo LinkSpreadsheet_Err


Dim varFileName As Variant
Dim strPassword As String
Dim db As Database
Dim oExcel As Object, oWb As Object

Set oExcel = CreateObject("Excel.Application")

DoCmd.SetWarnings False

Set db = CurrentDb()

strPassword = "rig"

varFileName = "F:\DATA\PUBLIC\Rig Schedule\Rig Schedule.xls"

Set oWb = oExcel.Workbooks.Open(FileName:=varFileName, ReadOnly:=True,_ Password:=strPassword, UpdateLinks:=0, IgnoreReadOnlyRecommended:=True)
DoCmd.TransferSpreadsheet acLink, 8, varFileName, True
oWb.Close SaveChanges:=False


DoCmd.SetWarnings True

LinkSpreadsheet_Exit:
oExcel.Quit
Set oExcel = Nothing

Exit Sub

LinkSpreadsheet_Err:
MsgBox Error$
Resume LinkSpreadsheet_Exit

End Sub



Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799

 
No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________