Transfer spreadsheets to access

[Urvi]

HELLLLLLLLLLLLLLLP !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I am trying to use the Transferspreadsheet option as,
docmd.transferspreadsheet

but it gives me an error.

What could be the reason?
I need to transfer data from 3 spreadsheets into access every month. What is the best and simplest way to do this?

Experts please reply soon. I am stuck.

[Norie]

What error?

And what's your actual code?

[OBP]

You could use this to learn from.
http://vbaexpress.com/forum/showthread.php?t=13555

[asingh]

Hi,

You could use the transfertext option in the macros section of Access. No need for code. It will push data into your access tables. You will need to specify...

1. Transfer type : choose "Import Delimited"
2. Specification name : the specs for the table being imported into.
3. Table Name : Your destination table name in Access.
4. File Name : Your source file name, the full path needs to go here.. c:\dbfolder\mydatafile.txt
5. Has Field names : If your source table has field names or not...

try..this..and let us know..

regards,

asingh

[Urvi]

The error i am getting is

"There was an error executing the command"

Any suggestions???

[Norie]

Yes, post your actual code.:)

[DarkSprout]

Here's some code to speed things along a little.

Public Function export_to_excel(txtPathAndFile As String, txtObjectName As String) As Boolean
'//returns True on success.
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, txtObjectName, txtPathAndFile
export_to_excel = (Err.Number = 0)
End Function

[Urvi]

asingh, thanks for the option of macro. that is working but i would like to put it vb since i need to do that every month. i will adopt the macro option if i am not able to solve my problem but i would still like to know why my code is not working ???

[Urvi]

code snippet :

DoCmd.RunSQL ("delete from tmpACK")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpACK", "Acknowledgements.xls", True
MsgBox "The Acknowledgement data has been updated", vbOKOnly, "ACKnowledgement Data"

[DarkSprout]

Does This Help? (Code To Blow The Data Out, From Access To Excel)

Place in a public module:=

Private Function FirstMonday() As Boolean
'ToUse: call on startup
'will output to Excel on First Monday of Every Month
Dim Desktop As String
Desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
If Val(Format(Date, "d")) < 8 And Format(Date, "dddd") = "Monday" Then
'//returns True on success.
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tmpACK", Desktop & "Acknowledgements.xls"
FirstMonday = (Err.Number = 0)
End If
End Function

[DarkSprout]

Does This Help? (Code To Suck The Data In, From Excel To Access)

Place in a public module:=

Private Function FirstMonday() As Boolean
'ToUse: call on startup
'will output to Excel on First Monday of Every Month
Dim Desktop As String, sql As String
Desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
If Val(Format(Date, "d")) < 8 And Format(Date, "dddd") = "Monday" Then
'//returns True on success.
On Error Resume Next ' <--- turn off for debuging
sql = "INSERT INTO tblRAWDATA" ' TableName
sql = sql & "SELECT [EST Extract$].*" ' Extract Items
sql = sql & "FROM [EXCEL 8.0;Database=" & Desktop & "Acknowledgements.xls" & " ;HDR=YES].[EST Extract$];" ' Path & FileName
DoCmd.RunSQL (sql)
FirstMonday = (Err.Number = 0)
End If
End Function

[Urvi]

thanks a lot ... solves my problem ..

[Urvi]

thanks a lot ... solves my problem ..

[DarkSprout]

thanks a lot ... solves my problem ..

You're Welcome
=DarkSprout=

[Urvi]

Thanks for the solutions. One more problem. can u help why the following is not working ???

tmpstr = " DoCmd.TransferSpreadsheet acImport," & strexceltype & ", " & strtablename & ", " & strexcelfile & ", " & blnfieldname
MsgBox (tmpstr)

(This displays the following :
DoCmd.TransferSpreadsheet acImport,acSpreadsheettypeExcel9,"TmpACK","D:\........\ack.xls",True )

DoCmd.TransferSpreadsheet acImport, strexceltype, strtablename, trexcelfile, blnfieldname
If i hardcode it is working but when using variables it is not giving an error but not importing any data.
Can u help??

[DarkSprout]

tmpstr = " DoCmd.TransferSpreadsheet ...

You can't use a variable to equal a Run Command := DoCmd...
Quote MicroSoft:

DoCmd Property

You can use the DoCmd property to access the read-only DoCmd object and its related methods.
expression.DoCmd
expression Required. An expression that returns one of the objects in the Applies To list.
Remarks

This property is available only by using Visual Basic.
Example

'The following example opens a form in Form view and moves to a new record.
Sub ShowNewRecord()
DoCmd.OpenForm "Employees", acNormal DoCmd.GoToRecord , , acNewRec
End Sub