Oct 11, 2005
Feb 1, 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:
I have aproblem with the range of this thing. I think I have a wrong synthax or something.
View 3 Replies
Oct 7, 2004
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
Apr 17, 2008
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 !
quickie -- i am away from my project at the moment - but does the TransferSpreadsheet overwrite existing data ??
View 2 Replies
Aug 25, 2004
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 )??
Hi, I am a new user.
View 5 Replies
Apr 3, 2007
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?
I am importing an Excel file of production data into a table using the TransferSpreadsheet method. Here is a snippet of the code:
View 5 Replies
Jan 9, 2008
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?
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.
View 14 Replies
Mar 7, 2005
Does anybody know whether there is a limitation on the number of fields that can be exported.
View 1 Replies
Jun 10, 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!
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.
View 3 Replies
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?