[AccessD] Link to spreadsheet

Kaup, Chester Chester_Kaup at kindermorgan.com
Thu Sep 23 10:38:55 CDT 2010


I came up with a solution. I gave up the idea of linking and instead was able to copy the data into an access table.

Here is what I came up with if anyone is curious.

Option Compare Database
Option Explicit

Sub LinkSpreadsheet()

On Error GoTo LinkSpreadsheet_Err

Dim xl As New Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim RetVal
Dim strSQL As String
Dim db As Database, rs As Recordset, r As Long

Set db = CurrentDb
 
With xl
 .Visible = True
End With

Set xlWB = xl.Workbooks.Open(FileName:="F:\Data\Public\Rig Schedule\Rig Schedule.xls", ReadOnly:=True, Password:="rig")
Set xlWB = xl.ActiveWorkbook

xlWB.Sheets("TA").Select
Set xlWS = xlWB.ActiveSheet
xlWS.Range("A3").Select

'delete the data in the access proposed TA Wells table and replace it with updated data
strSQL = "DELETE * from [tbl Proposed TA Wells2];"
DBEngine(0)(0).Execute strSQL, dbFailOnError
   
' exports data from the active worksheet to a table in an Access database
' get all records in a table
Set rs = db.OpenRecordset("tbl Proposed TA Wells2", dbOpenTable)

r = 3 ' the start row in the worksheet
Do While Len(xlWS.Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
    With rs
        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("Priority") = xlWS.Range("A" & r).Value
        .Fields("Field Area") = xlWS.Range("B" & r).Value
        .Fields("Well") = xlWS.Range("C" & r).Value
        .Fields("Inactive Date") = xlWS.Range("D" & r).Value
        .Fields("Days Down") = xlWS.Range("E" & r).Value
        .Fields("Date Approved") = xlWS.Range("F" & r).Value
        .Fields("Days Scheduled") = xlWS.Range("G" & r).Value
        .Fields("CSG Pressure") = xlWS.Range("H" & r).Value
        .Fields("Pattern/ Well BOPD") = xlWS.Range("I" & r).Value
        .Fields("Maint Fee List") = xlWS.Range("J" & r).Value
        .Fields("KM Owned Expiration Date") = xlWS.Range("K" & r).Value
        .Fields("Status") = xlWS.Range("L" & r).Value
        .Fields("Engineer") = xlWS.Range("M" & r).Value
        .Fields("Comments") = xlWS.Range("N" & r).Value
        .Update ' stores the new record
    End With
    r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing

xlWB.Close SaveChanges:=False

End Sub

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Tuesday, September 21, 2010 6:36 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.
_______________________________________________________________________________________



Thanks for checking that, one issue out of the way.  Next suspicion (as has been discussed) would be transfer spreadsheet method.  I move data between Excel and Access a lot and never use transferspreadsheet,  I don't like using this at all.  You might be better off using other ways. Some of them are slower, but much more reliable.  In my experience transfer spreadsheet can be problematic and frankly, not to be trusted.

have a look here, it might help

<<http://www.excelyourbusiness.com.au/FormulaHelp.htm#FormulaExcelToAccessTnf>>

this is probably even closer to what you need

<<http://www.excelyourbusiness.com.au/ms_access_page.htm#AccessImportFromExcel>>

 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 11:55 PM
To: Access Developers discussion and problem solving
Subject: 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
_______________________________________________________________________________________

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





More information about the AccessD mailing list