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.
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:
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 !
I am using the DoCmd.TransferSpreadsheet statement that successfully exports a query result set into excel. I was wondering is there a way to get this result to populate certain fields of an existing excel spreadsheet that I have created and need the information for. Right now the query just creates a new worksheet in the specified workbook.
Hi. I'm using the above in some code, but I want the user to be able to choose where to save and what to name the spreadsheet.
I have a table that is exported to an Excel Workbook, however i want to sum the values in a particular column in this workbook automatically (i.e. column P)
I have written some code in Excel vba that will sum the values for me.
What i was trying (or thinking) of doing was creating a template in Excel (using the code to sum above), then using the transferspreadsheet to copy the data from my table in the database into the template spreadsheet so that the sum will automatically be caculated without me having to even open the spreadsheet.
I've tried this using the transferspreadsheet, and the data from my table is not being transferred to my spreadsheet, has anyone any ideas on this? am i going about this the right way or is there an easier way?
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 )??
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?
I am using a sub to create a table. Then I use the files system object method to create a directory
following to save the table to a directory:
parfolder = "C:SWIPReport Archive" & parsave & "" & pargrid & ""
Set fso = New Scripting.FileSystemObject
then I use the following to save the file
parfile="COMPLIANCE_ISSUES_WORKSHEET" & parfolder
DoCmd.TransferSpreadsheet acExport, 3, "COMPLIANCE_ISSUES_WORKSHEET", parfile, True
And I get the following error message:
runtime error 3207 Cannot Update. Database or Object is READ ONLY
I then check the folder attributes of the pargrid folder I created above and it is indeed Read Only so my question what sets this read only attribute and can a programatically change it to copy this file?
Hi, I have setup a transferspreadsheet macro that moves my data into a spreadsheet.
The problem is, i have to have a spreadsheet setup for the data to go into.
What i would like it to do is go into a template or spreadsheet and bring up the save as dialog box so that i could choose a name for the spreadsheet.
This way i don't have to create 12 spreadsheets and duplicate them 12 times as well as the macro.
Would anyone know how can I have a prompt for the file name when Inporting or Exporting Excel files? I've tried leaving the argument blank but it doesn't seem to like that.
I am trying to create a macro to import information from excel worksheet to a table in access. The table contains a primary key (emp number) and when I try to add the data to the field termdate from Excel i get the error that data was lost. I understand that the primary key is the problem. I only want to add the dates to the field terdate for each employee that has been termed. I have multiple dates to enter on a daily basis and was trying to make entry quicker. I put the emp num and term dates in a spreadsheet to import to the table.
Can you give me an idea of how to do this??
I am importing data from a spreadsheet and keep getting an error, the error reads:
Runtime error 2391
Field F1 doesnt exist in destination table "myTableName"
I am aware that F1 doesnt exist in destination table but neither does it exist in the spreadsheet!
I have used TransferSpreadsheet method before with no trouble. I am using the below line of code
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTableName, strFilePath, _
True, strInput & "!" & "B2:B20"
strInput is the worksheet name and teh range contains the required data.
When I add the F1 field to my table it imports 18 blank records?
Any help appreciated.
I routinely use the TransferSpreadsheet method of DoCmd to get new data into Access. Prior to doing so however, I have to open a particular file in Excel and run a macro there first. Is there a way to automate that process within Access? A way to open & manipulate an Excel file in the background programmatically prior to invoking the TransferSpreasheet method?
OK..I read thru a couple of the previous posts regarding this action...and I'm not sure I tracked down the answer...I've been spending alot of time in vba for excel; so my thought process is sort of stuck there, so please bear w/ me...
I've created the database I need and imported several excel sheets, works just fine.
I have an excel workbook that I add sheets to on a daily basis (exact same info types and formatting). the name convention on the individual sheet tabs is just the current date and (1), (2) etc....
My issue is wanting to automate thru a macro or module? using the 'TransferSpreadSheet' command. Can this be used to transfer the items daily,JUST THAT DAY's SHEETS?.... that are new to the workbook for that day...w/out having to change the syntax to specify particular worksheets??
I'm using the "transferspreadsheet" method but I'm looking for a way to browse the drives to choose the path and file name to transfer. Has anyone found a way to do this. Any help would be appreciated.
I am using vba to export to a spreadsheet. I knwo I need to use the transferspreadsheet code but how do I tell it to export to a specific sheet name. Then I will be doing addtional exports to this woorkbook and I want to create additional sheets also. So it may have 3 sheets 1 called Employees 1 called supervisors, 1 called deparments. So I want 1 workbook with those 3 sheets.
I am having a little problem with a transferspreadsheet function...
Basically, the transferspreadsheet send the table to a excel workbook..and it automatically replaces the one that I already had there...which is what Iam trying to do...
My problem is that the information that is exported all has a ' in front of it (indicating text)
My spreadsheets are not liking this and there is a lot linked to this "exported" table
here is my function:
Code: Private Sub lblMOSI_Click()
DoCmd.OpenQuery "qryMOSIMakeTable", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblMOSI", "C:PT/DMOSI"
Call Shell("Excel.exe C:PTDMOSI.xls", 1)
If anyone has some insight or anything, help would be greatly appreciated
I can successfully export a table from access to excel with the extension of XLS, but when I change the extension of the filename to "ORDERS20050301.ord", it gives me a run-time error of 3027 - Cannnot Update. Database or object is read only.
Is there a way to prevent the transferspreadsheet to default to the XLS extension, and give the new extension to however I want?
Can access be able to change the filename of the most recently created XLS and change it to ORD?
I am currently taking info from SAP and exporting it into Excel then importing the data to Access through the TransferSpreadsheet function. How can I get a text field in the Excel file to import to Access as a number field. I'm losing the decimal value.
I wanted to be able to transfer the contents of a select query to a spreadsheet, and later send the contents of the same selecet query (after the contents may have changed) to the same file, but to a different worksheet.
If I reexecute the same command it simply overwrites the data in the first worksheet to which data had been transferred, The documentation suggests that it will select another worksheet. Is there a way to specify the worksheet? The documentation says that on an export you may not use the range parameter.
I also made a transfer and then renamed the worksheet to which the data had been transferred. When I transferred again with the same transfer statement it overwrote the renamed worksheet.
Pre-2007, when using transferspreadsheet to import an Excel file, Access would create a new table using column names of F1, F2, F3,etc. But in 2007, when using the same transferspreadsheet function, it creates the table w/ column names of 1, 2, 3, etc. Why did this change and is there a parameter I don't know about that allows me to choose the new tables column names? NOTE: I do not want to use the first row of the excel file for the column name. I need them to be generic, such as the F1 or 1, but I need it to always be the same no matter what version of Access I'm running. Can anyone help? TIA!
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?
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.
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.
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?
When exporting a query using the TransferSpreadsheet function I have found that you cannot overwrite a file. This causes a bit of a problem as the function is used to automate a weekly task that overwrites a file with updates.
Any help would be appreciated
When I use the TransferSpreadsheet function (Export) and I open up my spreadsheet all columns that are not all dates or number have an apostophe in front of the value. I understand what it is doing, but how can I stop this from happening?
I am working on a contacts database that requires reports,letter and the such to be printed from a myriad of selections. I have managed this to an extent but due to the sheer number of reports and letters that need to be converted i wanted to be able to export the results to excel so the users could at least mail merge while i work on getting the rest of the reports done (amongst everything else)
The sticking point is that access only allows you to export (or import) for that matter from tables of select queries. Now i have a select query that i use for the reports but its then limited to the requied data by the use of the where clause in the docmd.openreport. docmd.transferspreadsheet unfortunately has no such functionality.
Can anyone think of a way around this? I just can't think of a way to insert the limiting parameters into the query before its exported.
Hello is it possible to import Non adjacent columns from excell into Access using the Transferspreadsheet Macro method. I am unable to write the Range value parameters for non adjacent cells.
Any help much appreciated my friends.
As I haven't mastered the world of VBA yet, I am limited to using macros.
I have created a macro to transfer a spreadsheet from Excel. No problem there but I was wondering if it was possible to create a TransferSpreadsheet macro that could prompt you for the path and file name of the Excel spreadsheet instead of having it pre-set in the macro at all times. Similar to entering in a value for a paramenter query.
When I export my queries to an excel spreadsheet, each individual query is exported to a seperate sheet in excel. I was wondering if there is a way I can do it so that all the queries are in one sheet and in the fields I need them to be.
Let me explain further... I am currently pasting about 20 queries on one Excel sheet and then they are formatted and calculated in different ways on multiple other sheets.
Let me know if there is a better, more efficient way of accomplishing this. Thank you!
Is it possible to do a transferspreadsheet from a workbook that has more than one worksheet? and if so, how would you specify what worksheet you wanted to reference?
Thanks in advance