DoCmd.TransferSpreadsheet

[Darth Vodka]

i have a little piece of code

Sub publish_data()
    Const UNC As String = "\\opssvr01\dmi\"
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tbl_COMMODITY", UNC & "TEST.xls", True


End Sub

which crashes Access EVERY time it runs



in 2000 or 2003 format

any ideas?

 

[KenHigg]

Does it give an error message?

 

[Darth Vodka]

it's the old "Microsoft Access has encountered a problem" crash

i can get the error out of visual studio...but it's all in hex or greek or something....

 

[KenHigg]

Does it crash if you change the location to something like c:\myfolder ?

 

[Darth Vodka]

Does it crash if you change the location to something like c:\myfolder ?

yes

and i've tried it in other databases

and compact/repaired

and new databases

 

[KenHigg]

I wonder if it has anything to do with the UNC constant name or maybe acSpreadsheetTypeExcel8? What happens if you alter/tinker with them?

 

[Darth Vodka]

I wonder if it has anything to do with the UNC constant name or maybe acSpreadsheetTypeExcel8? What happens if you alter/tinker with them?

no joy so far...



tried a few paths including the c: and tried a few different excel parameters

might have missed a combo that works, but it's a pain having the whole lot crash everytime

 

[KenHigg]

Have you tried exporting another, real simple set of records?

 

[Darth Vodka]

Have you tried exporting another, real simple set of records?

yes and this one is simple anyways...

 

[KenHigg]

I'm beginning to think you need to do a re-install...?

 

[tranchemontaigne]

Have you tested the code on another computer?

This will help you determine if your installation of MS Access is corrupt, or the code is not being handled well.
________
TRICHOMES

 

[MSAccessRookie]

i have a little piece of code

Sub publish_data()
    Const UNC As String = "\\opssvr01\dmi\"
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tbl_COMMODITY", UNC & "TEST.xls", True
 
 
End Sub

which crashes Access EVERY time it runs



in 2000 or 2003 format

any ideas?

I am sure you must have tried this, but since no one else has suggested it yet, I will, and hope it is not a waste of time. Have you tried adding some error handling and displaying the error that is occurring?

Sub publish_data()
[COLOR=black][FONT=Verdana]On Error GoTo publish_data[/FONT][/COLOR][COLOR=black][FONT=Verdana]_Err[/FONT][/COLOR]
 
    Const UNC As String = [URL="file://\\opssvr01\dmi\"]\\opssvr01\dmi\[/URL]    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tbl_COMMODITY", UNC & "TEST.xls", True
 
[COLOR=black][FONT=Verdana]publish_data_Exit:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Exit Function[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]publish_data_Err:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   MsgBox Error$[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Resume publish_data_Exit[/FONT][/COLOR]
 
End Sub

 

[Darth Vodka]

I am sure you must have tried this, but since no one else has suggested it yet, I will, and hope it is not a waste of time. Have you tried adding some error handling and displaying the error that is occurring?

it's not handling an error, it's crashing Access

 

[Darth Vodka]

Have you tested the code on another computer?

yes, several

 

[KenHigg]

So you can't get a TransferSpreadsheet to work on any computer?

 

[Darth Vodka]

So you can't get a TransferSpreadsheet to work on any computer?

no, which is very strange

tried different everythings, i have no instance of it working. maybe it's a clash with 1997/2000 or a clash with something else we all have installed

perhaps Access needs a service pack or something



will have to get a reinstall i think...

 

[tranchemontaigne]

just for giggles, have you tried using a different path e.g. "c:\temp" to see if it makes any difference?

if this fails, I'd start thinking about using a different transfer method...


www.zmey.1977.ru/Access_To_Excel.htm
________
MAC GAMES

 

[chergh]

Try installing this: http://support.microsoft.com/kb/902445

It doesn't have you exact issue but if it isn't working on any computers then it does sound like you need a patch. Alternativley use the copyfromrecordset method.

 

[Olav]

Sorry for digging up old threads, but I had some problems with this function too. However, I found out that the reason why Access crashed was that the spreadsheet was locked by another application.

I found this link: http://support.microsoft.com/kb/209189 and wrapped the DoCmd.TransferSpreadsheet function:

Function TransferSpreadsheet(Optional TransferType As AcDataTransferType = acImport, _
                             Optional SpreadsheetType As AcSpreadSheetType, _
                             Optional TableName As Variant, _
                             Optional FileName As Variant, _
                             Optional HasFieldNames As Variant = False, _
                             Optional Range As Variant, _
                             Optional UseOA As Variant) As Boolean
 
    On Error Resume Next
    ' Try to open the file.
    Open FileName For Binary Access Read Write Lock Read Write As #1
    Close #1
    ' If an error occurs, the document is currently open, or maybe something else is wrong..
    If Err.Number <> 0 Then
        ' Display the error number and description.
        MsgBox "Error in function ""TransferSpreadsheet""" & vbCrLf & vbCrLf & "Error #" & Str(Err.Number) & " - " & Err.Description, vbCritical, Err.Description
        Err.Clear
        GoTo TransferSpreadsheet_err
    End If
 
    'Execute the real TransferSpreadsheet function with the validated filename.
    DoCmd.TransferSpreadsheet TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA
 
    'Notify the caller that the function went well.
    TransferSpreadsheet = True
    GoTo TransferSpreadsheet_end
 
TransferSpreadsheet_err:
    'Notify the caller that the function failed.
    TransferSpreadsheet = False
 
TransferSpreadsheet_end:
 
End Function

With this function, just remove the "DoCmd." part. And optionally do some tests on the function if your code depends on a successfull execution. -This is very useful if you are exporting to a public spreadsheet that other users may have opened.