[AccessD] Link to spreadsheet

Brad Marks BradM at blackforestltd.com
Wed Sep 22 14:07:11 CDT 2010 

I also have had bad results with "transferspreadsheet"

I have struggled a bit with exporting Access 2007 Reports to Excel.  

Exporting the report's underlying query works nicely, but this method does not take into account any report filters that may be turned on.

Here is a very simple method that seems to work nicely for the reports that I am currently working with.

Open the report
On the Ribbon choose - External Data / Export / Excel
Run the actual export to Excel (for a first time test)
Save Export Steps (This will create a "Saved Export" - give it a name of your choice.  I chose "Export-Report1" for this example)


In VBA code I then use DoCmd.RunSavedImportExport "Export-Report1" to run the Saved Export via a button.
This seems to work nicely.

Even though this technique is working well, I am curious as to what Access is doing behind the scenes

Brad

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

More information about the AccessD mailing list