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.
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 !
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.
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?
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?
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.
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 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!
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 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.
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.
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!