[AccessD] Link to spreadsheet

Tue Sep 21 10:22:17 CDT 2010

Thanks for the good explanation. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Tuesday, September 21, 2010 10:02 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Link to spreadsheet

Chester,
> DoCmd.TransferSpreadsheet acLink, 8, varFileName, True
Parameter 4 is supposed to be the filename. So setting this to True obviously give the mentioned error (-1 = True).
Parameter 3 is supposed to be the name of the linked table, which in your case might be "Link to Rig Schedule"
So the right syntax should be:
DoCmd.TransferSpreadsheet acLink, 8, "Link to Rig Schedule", varFileName, True

But if your Excel file is password-protected then this code will produce another error telling that the file couldn't be decrypted.
As I read your code you are trying to solve this protection issue by opening the file via automation supplying the password.
Problem is that this won't do any good to the DoCmd.TransferSpreadsheet because the TransferSpreadsheet method doesn't use the instance of the file you have just opened via automation.
I don't think you can use DoCmd.TransferSpreadsheet at all create a link to a password protected Excel file.

Maybe you could make the link by creating a new TableDef from your oWb and then appending it to the database's TableDefs collection.

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Kaup, Chester
Sendt: 21. september 2010 15:55
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Link to spreadsheet

Code shows that the file exists. Changing dim on varFileName to String made no difference. Still getting the error

The file '-1' does not exist

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Monday, September 20, 2010 7:12 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Link to spreadsheet

_______________________________________________________________________________________

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

Hi Chester,

Maybe obvious, but the first thing I would check is that the path and file name is 100% correct.  If you are on a LAN I would recommend you change the drive to a URL name rather than a drive letter.  You can use a function to test if the file exists first before attempting to open if.  If the function below returns true the file exists.  I would suggest you test for the file first.  If the function passes you can then start checking other options for the failure.

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

If CheckFileOrDirExists(varFileName) = True Then
	wb.open blah blah..
End If

'===========================================================
Function CheckFileOrDirExists(PathName As String) As Boolean
     'Macro Purpose: Function returns TRUE if the specified file
     '               or folder exists, false if not.
     'PathName     : Supports Windows mapped drives or UNC
     '             : Supports Macintosh paths
     'File usage   : Provide full file path and extension
     'Folder usage : Provide full folder path
     '               Accepts with/without trailing "\" (Windows)
     '               Accepts with/without trailing ":" (Macintosh)

    Dim iTemp As Integer

     'Ignore errors to allow for error evaluation
    On Error Resume Next
    iTemp = GetAttr(PathName)

     'Check if error exists and set response appropriately
    Select Case Err.Number
    Case Is = 0
        CheckFileOrDirExists = True
    Case Else
        CheckFileOrDirExists = False
    End Select

     'Resume error checking
    On Error GoTo 0
End Function
'===============================================================================

-----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.
_______________________________________________________________________________________

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

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

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