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.
Hi, I was wondering if there was a way for a report to be automatically saved as an excel file. Also, the Access database is stored on the company's server so would you be able to choose the path where the excel file would be stored?
Any definitive way of exporting a query to an Excel file and then saving it as a new file without saving over the original.
I've tried to remove any confidential info from the code below so it's not exactly the same.
Code: Dim XLApp As Excel.Application Dim XLSheet As Excel.Worksheet Dim tmpRS As DAO.Recordset Dim strFolder as String strFolder = ("C:Profiles"& [Name] & "")
[Code] ....
The error seems to be with the SQL statement although that may just be the first error that it got to. I read that you can't refer to a Query if it has a criteria and that you have to write the SQL directly into the code.
I am trying to write some code to open an existing excel form, populate it, and save it with a custom filename in a directory that is built using variables I have declared.
The only part I am having difficulty with is setting the pathname. Here is my code:
Code: 'Build path to save file to spath = "S:ContractsGeomatics LABSigned" & Company 'Build File name Filename = "" & reservationNumberforForm & " " & Company & "-Payment Schedule"
'Save the payment schedule to the S: drive 'xlApp.ActiveWorkbook.SaveAs Filename:="S:ContractsGeomatics LABSigned" & Filename, FileFormat:=xlOpenXMLWorkbook xlApp.ActiveWorkbook.SaveAs Filename:="" & spath & " " & Filename, FileFormat:=xlOpenXMLWorkbook xlApp.ActiveWorkbook.Saved = True
I am using Access to save an Excel file in a particular directory. The code was created about 10 years ago and hence saves the file with .xls extension and I would like to save with a .xlsm extension. If I just change the .xls to .xlsm, in the code below, the file is saved but the Compatibility Checker comes up (I would like to stop this) and when the file is reopened I get an error message stating there is a file extension problem and the file won't open.
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 !
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.
where 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:
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?
I searched the archive and didn't find quite what I was looking for, so..
I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.
I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.
Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!
A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.
I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.
Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject
I have an excel file linked to a table in Access. Several fields are date data types in excel but are showing up as text fields in Access.
My real goal is to do a comparison between two tables, but only if the date of the one piece of data is newer than the other. I had planned on comparing the two date fields but even though I have formatted the date fields in my excel file to be "Date", when I look at the design view of my table it is showing up as "Text" and therefore I am unable to do this comparison.
I'm not sure if it's just something that I'm missing but maybe someone else knows an easy fix to this. I know this is probably a simple question, but I did search the forum and didn't find a thread that specifically dealt with this issue.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
Hello, sorry to post again my question but just cannot find a solution. I have a table and would like to copy data into a template in excel. I know that the code will have to make a copy of the template and then copy the data into the new workbook into sheet1.
My table (table1) has 3 fields: SSN, FNAME and LNAME. I want to copy these fields into cells B1 (for SSN), B2 (for FNAME) and B3 (for LNAME). I will use a combo to select the recorset to copy.
My problem is how to copy data into the template. I understand that it is necessary to run a copy of the template and then copy the data into the new xls file.
Is there a way I can do this via code? Code help is appreciated. Thank you.
thought I was ok at access, obvously not. Think 'briefcase' has messed with my db a bit so started using a backup with ext .BAK. Want to save whole db as another name with ordinary extension. Will only let me SAVEAS on bits of db?
Sorry, I know this is a bit simple but that's about right for me.
Hi all, when the users are saving the form, i want to make sure they have filled in certain fields. On previous forms my code has worked, but for some reason on this form it won't, i usually put:
If tankID = "" Then MsgBox ("Please Enter the Tank ID") tankID.SetFocus End If
Can anyone explain why it may not be working, thank you
I have a table which brings together a number of bits of info from other tables.
Part of the form is acting weird; The user selects an 'applicant', and a 'class'. On the form these are both combo boxes, with info coming from two tables.
Some properties of the two fields: SELECT Classes.ClassCode, Classes.ClassName FROM Classes; Bound Column = 1
SELECT Applicants.ApplicantCode, Applicants.ApplicantName FROM Applicants; Bound Column = 1
Now, I want it to store the ID's of both (rather than the name). Both of these combos have the same properties; however when I look at the table, the applicants ID is being stored, but the class NAME is being stored in its field instead of ID>
Hope someone out there can help. I have a form (frmNewExceptionRepot) where the record source is a table (tblExceptionReport) there are a fair few controls on the form from this table and some controls that are based on queries etc
I have a cmd button - that takes the value of text boxes from a subform on this form and saves them to tblExceptionReport It also saves the record in general
This all works fine, however I am having a problem saving the value of a list box on the form (not subform) List156 to the table tblExceptionReport. The record source of the list box is a query that only ever produces one result, and its always the first row that needs to be updated to the table.
The data is displayed correctly when the form is loaded, but the value will only be saved if you click on the list box before the record is saved. I understand the theory behind this, as its a listbox an item need to be selected. However is there a way to tell it to select the first row of this list box as the value? I have tried various ways around this, but just can't figure it out. Should I be using a text box instread? I had thought about basing the form on a query that contains all the information, so this list box can actually be a text box, but then it makes the recordset not updateable...which means lots more code on the save - if this is the only way round then fair enough! Any ideas or help much appreciated!
I have highlighted in red the code currently in use to try and do this. (which works if you click on the list box before running the code.:confused:
Private Sub Command242_Click() 'save record DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'Save the current dates and informaiton to the new record DoCmd.RunSQL "Update tblExceptionReports set CurrentVRF = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastVRF], CurrentLandingSlot = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastLandingSlot], CurrentPlanComplete = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastPlanComplete], CurrentReqtsSolWShop = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastReqSolWS]," & _ "CurrentAlignmentReview = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastAlignment], CurrentDSBBidPictureEvent = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastDSBBigPic], CurrentCostBenefitEvent = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastCostBenefitWShop], CurrentDSBDetailedEvent = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastDSBDetail]," & _ "CurrentITExecutiveReview = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastExecRev], CurrentITSupplierPropIssued = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastITSuppPropIss], CurrentSellByDate = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastSellByDate], CurrentViabilityReport = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastViabilityReport]," & _ "CurrentProgramBoard = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastProgramBoard], CurrentProposedImplementation = [Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![Proposed_Implementatation_Date], OverallRAG = [Forms]![frmNEWExceptionReport]![List156], CurrentSpendBudget = [Forms]![frmNEWExceptionReport]![List240]" & _ "WHERE tblExceptionReports.ExceptionReportID = [forms]![frmNEWExceptionReport]![ExceptionReportID]"
I'm trying to create a database for a taxi-type service that runs on thursday, friday and saturday nights. My question is: Is there a way to save the database at the end of the night separate from the previous nights, and so that the database is empty for the next night, but reports can be viewed for all of the nights combined?
I have a annoying problem. Since yesterday whenever i make changes to anything in my database, it doesn't ask me if i want to save the changes, it just does it automatically. It is annoying, as sometimes i am testing things, say on a query and if it doesn't work, i just go out of it and don't save the changes and have my original version.
I have another Access database and this works fine, with the pop up message asking if i want to save the changes or not.
Can anyone help, or point me in the right direction, thanks.
I seem to be having problems saving my database files (.MDB) as .MDE files.
I have a database that only contains data (tables).
A second database is used as the interface to this data (it contains forms, reports, and uses linked tables to the data database).
This secong database file is then copied and used my a few users, so that everyone is accessing the same set of data.
I am trying to save the second database as an .MDE file, but under the Tools, Database Utilities option... 'save Database as an MDE' is never highlighted as a viable option. Is there some trick (or conditions) to enable this option?
I am basically doing this so that I can distribute the MDE version of the interface so that users will have limited / no access to be able to modify the code (or get into the table?).
Thank you for any advice or directions you may be able to pass on.
I'm new at access but getting the swing of it. It is a pretty cool toy.
I have a table that I am adding various columns of data. Once added I move the columns to where I want them to be. I am hitting the little save disk on the top toolbar but when I reopen the file the columns are not where I placed them. I'm not sure why this is happening. Any thoughts?
