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