Export: use SendObject or Transfer?

[ggregg]

I am trying to automate exporting data and emailing it to another branch to
import it.
I can use TransferText to Export to a file outside the database but can't
use SendObject to email it.
So I have been trying to use SendObject in XLS format. It sends the data in
spreadsheet format which is fine, but when I import it at the other branch
through Transferspreadsheet, it includes the headings as data.
TransferText works great because I can set the Import/Export specifications
to not include the headings but then I can't automatically email it.
Is there a way to set the Import/Export specifications for
Transferspreadsheet?
Or is there another way around this?
Thanks you for any suggestions

 

[Tom Wickerath]

Hi Greg,

I can use TransferText to Export to a file outside the database but can't
use SendObject to email it.

Correct. SendObject is designed to send an object (table, query) in the
current database, not an external file.

So I have been trying to use SendObject in XLS format. It sends the data in
spreadsheet format which is fine, but when I import it at the other branch
through Transferspreadsheet, it includes the headings as data.

You could link to the XLS file, and then base an append query on this linked
Excel spreadsheet, in order to append (add) the new records to your target
table. I generally find this method works much better than trying to import
into a new table. By running an append query, you can use type conversion
functions, if necessary, to coerce data into the correct data type. For
example, if a date field happens to import as text, you can use CVDate to
coerce it into a date/time datatype (assuming, of course, that the string
representation is a valid date in all cases).

TransferText works great because I can set the Import/Export specifications
to not include the headings but then I can't automatically email it.

You can if you are using Outlook as your e-mail client (Outlook Express will
not work). I show how to do this in a Word document that I call "Access
Links" (I think it's approx. page 19 or so). You are welcome to download a
zipped copy, here:

http://www.accessmvp.com/TWickerath/downloads/accesslinks.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[ggregg]

Thank you so much for the useful information

Hi Greg,


Correct. SendObject is designed to send an object (table, query) in the
current database, not an external file.


You could link to the XLS file, and then base an append query on this linked
Excel spreadsheet, in order to append (add) the new records to your target
table. I generally find this method works much better than trying to import
into a new table. By running an append query, you can use type conversion
functions, if necessary, to coerce data into the correct data type. For
example, if a date field happens to import as text, you can use CVDate to
coerce it into a date/time datatype (assuming, of course, that the string
representation is a valid date in all cases).


You can if you are using Outlook as your e-mail client (Outlook Express will
not work). I show how to do this in a Word document that I call "Access
Links" (I think it's approx. page 19 or so). You are welcome to download a
zipped copy, here:

http://www.accessmvp.com/TWickerath/downloads/accesslinks.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________