Help with the DoCMD.Transferspreadsheet command

[Vigneshrs87]

Hi,

I need some help on the DoCmd.transferspreadsheet command, Im currently using the below code.

Sub Update()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim obj As Object
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set obj = CreateObject("access.application")
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=C:\Documents and Settings\220409\My Documents\Database21.accdb;"
rs.Open "select count('ID') from Hi", cn
a = rs.Fields(0).Value
obj.docmd.TransferSpreadsheet Transfertype:=aclink, Spreadsheettype:=9, TableName:="Hi1", Filename:="D:\220409\Defect Tracking sheet\book1.xls", HasFieldNames:=True, Range:=MyRange
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Please letme know what am i doing wrong, I get an error saying that "The command ot action 'Docmd.Transferspreadsheet' isnt available now.

Thanks for your help.

 

[boblarson]

What is it you are really trying to do? Your code is a bit confusing because you are all over the place. Are you trying to link a spreadsheet to an Access database that is not the one that this code is in?

 

[Vigneshrs87]

Hi boblarson,

thanks for your reply, Yes im trying to link the sheet.

Ive found out the mistake, I have declare an object object before using the docmd.transferspread sheet command.

Is there a way to link a spreadsheet to access database without using the dcmd command so that i dont have to create an object.

Thanks in advance

 

[boblarson]

No, you would have to create the object and use it. But if you are trying to link the spreadsheet at the same time you have the database open it might not allow you to unless you open the database as exclusive. So I would use DAO instead of ADO to accomplish it.

 

[Vigneshrs87]

Hi Thanks for your reply.