Oct 11, 2005


Bit stuck here but bet it is really stupidly simple I have the following code to export a query into a spreadsheet which worked fine but after I added the path name which is in bold it no longer works.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "NME With Company Code", C:My DocumentsTDL Update(Format(Date, "mmmm")) & "'s TDL Information", True

what am i doing wrong what I am looking for it to export this to a specific folder in my documents.

Thanks in advance

:confused: :eek: :confused: :eek: :confused:

View Replies



Feb 1, 2005

I have aproblem with the range of this thing. I think I have a wrong synthax or something.

I need to have the first 120 records of columns A and D
The first two lines aren't records but titles

So I had:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "ExcelTEMP", mijnFile, False, "A3:A122;D3:D122"

Access tels me there is somthing wrong with the range, though it works in Excel :confused:

View 3 Replies View Related

Filter On TransferSpreadsheet ??

Oct 7, 2004

Hi All,
I'm new on this forum and not that experienced in coding.
I am making an export to Excel and am wondering if (and how)
I can set a Fliter on output.
I have a db for keeping scores for contests, this db is used by several people in diferent locations,
so I would like them to export there own contest and me importing this in my own db. I need
to have a complete (all contests) db for competion rankings.
Thanks for replying !

View 3 Replies View Related

TransferSpreadsheet Method Overwriter ?

Apr 17, 2008

quickie -- i am away from my project at the moment - but does the TransferSpreadsheet overwrite existing data ??

i have the transfer set up in to a xls ( this works) but if i change the source will it over write the transfered data (I want it to do this )??


View 2 Replies View Related

Transferspreadsheet - Saving Excel

Aug 25, 2004

Hi, I am a new user.

I need to:
Take the result of a query and export it to excel.
Manipulate the data in excel.
Re import the same data into a table
And finally put the table into an existing form

I have succesfully created the macros to
Export the data and
Re-import the data

BUT I have to mannually open the spreadshhet and save it
for the RE-Import to reflect the updated data from the ss.

Is there anyway to automate this saving process?

View 5 Replies View Related

TransferSpreadsheet - Data Conversion Error

Apr 3, 2007

I am importing an Excel file of production data into a table using the TransferSpreadsheet method. Here is a snippet of the code:

DoCmd.TransferSpreadsheet acImport, , "tblMPSDATA", _
"" & stFilePath & "", True, ""

Everything works fine except the column which contains 'Material' holds both numeric and alpha-numeric values. For example these are both material numbers: 156952 and 1238707-202. The data in this column is formatted as General. The data type for Material in tblMPSDATA is Text, 18 character length. The alpha-numeric materials are all at the end of the file. When I import, an error table is created listing the alpha-numeric materials with the error 'Type Conversion Failure'. But if I have an alpha-numeric material in the first row of data then everything is imported just fine.

I have set up a nice little popup form with a file path and command button for controlling the process of bringing in this data. I really do not want to have to add special instructions about making sure the Excel data is sorted in a certain manner prior to importing. Any thoughts on why Access is not treating everything in this column as text?


View 5 Replies View Related

DoCmd.TransferSpreadsheet Does Not Export All Fields

Jan 9, 2008

I used the DoCmd.TransferSpreadsheet method to export from an Access 2003 table to Excel. The table had around 440 fields but only 230 (column iv) got exported.

Does anybody know whether there is a limitation on the number of fields that can be exported.


View 14 Replies View Related

TransferSpreadsheet - Defining Range Syntax?

Mar 7, 2005


I am trying to auto-import data w/ a single-click from several Excel sheets. A sample line:

DoCmd.TransferSpreadsheet acImport, , "tblTS1", filename, True, Product!A1:H100

tblTS1 - tbl where I'm putting the data
filename - c: est.xls
Product!A1:H100 - range of data I want in sheet Product

I am having problems w/ the "range" portion of Transferspreadsheet (I know it's this portion, because it works when I pre-name the range in Excel). I have tried all sorts of variations on the syntax:

"Product!A1:H100" or 'Product!A1:H100'
"Product" & ! & "A1" & : "H100"
"'Product'" & ! & "A1" & : "H100"
Product & "!" & A1 & ":" H100

I get the error msg:
The MS Jet database engine could not find the object '$:'. Make sure the object exists and that you spell its name and the path name correctly." or "Syntax error"

I wanted to mistake-proof the data transfer, so I did not want to:
- predefine the range in Excel (under InsertNameDefine)
- have the user enter values in text boxes

Any help w/ the syntax would be greatly appreciated!

dvs :confused:

View 1 Replies View Related

Transferspreadsheet - Numeric Field Overflow

Jun 10, 2005

I have been using a transferspreadsheet cmd for a while now in Access 2003, but when I recently split the database so that the table the import is going to is now linked rather than residing in the same mdb file, I get a numeric field overflow error. I manually made all the values in the import 0, converted it to text, but got the same error.

When I imported the table back into the file, rather than having it linked, the transfer worked fine.

Can you not run the transferspreadsheet command on a linked table or am I missing something?

View 3 Replies View Related

Copyrights 2005-15, All rights reserved