I created a form with lots of conditional formatting that did pretty much everything I wanted it to do. The only problem is that it takes about 4 full minutes for the form to open.
As an experiment I am reluctantly now trying to display the results in Excel. I have created a template xls sheet and all I want to do is, on the press of a button, copy the results from my query and paste them in to cell a1 of my spreadsheet.
I found the following code online which I am trying to adapt.
Code:
Private Sub update_tracker_Click()
Dim XL As Excel.Application
Dim wbTarget As Workbook
Dim qdfResults As QueryDef
Dim rsResults As Recordset
'Set up refernce to the query to export
[Code] ....
My limited knowledge however results in a couple of errors.
The first error User defined type not defined error appears at the very first line of my code
Code:
Dim XL As Excel.Application
The next error occurs as I am not sure how to reference the query to export. The query is called 2014 Resources and outputs also to a form called 2014 Resources.
What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.
I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"
What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:
Code:
Option Compare Database Public Function ExportAdvanced() Dim strWorksheet As String Dim strWorkSheetPath As String Dim appExcel As Excel.Application Dim sht As Excel.Worksheet Dim wkb As Excel.Workbook Dim Rng As Excel.Range Dim strTable As String Dim strRange As String Dim strSaveName As String Dim strPrompt As String Dim strTitle As String Dim strDefault As String
I have info on my access form which is located here....
Code : Forms![Front Page]![Site 2 Owner]
and here
Code : Forms![Front Page]![Postcode S2]
I would like to export this information into cells B2 & C2( individually and respectively) on the excel spreadsheet which I have saved as a template here...
Code : C:UsersmedesktopAutoExcel Auto
Is this easily achievable? To be honest I will be using it to fill in about 12 cells but how it would be done for the first two i can just modify it as necessary.
Its been a few years since I used Access, and despite searches I cant find what I'm looking for. I have a VERY simple form, with only one record on it - "address". I have some print buttons which will print that address to different size labels, and some navigation items, but only one actual record - which is "address" (in the table, this address is a MEMO)
I would like to have a command button that when clicked copies the current record, and opens an existing Excel Spreadsheet and pastes that address into (for example) cell C8
Can it be done easily? Is there a DoCmd.TransferSpreadsheet kind of secnario that works with current record only?
All I need to do is copy an excel file from a location (while coping the file, the location of the source file remains same all the time)and paste that excel file where ever I want (Browser Option) from an Access VBA.
I am able to use DoCmd.TransferSpreadsheet to export data from Access to Excel, however, I want to be able transfer data into a specific sheet within an Excel template (e.g. Tasking.xls), that will then save under a different name (e.g. Tasking 20140429.xls). The other sheets within the Excel template contain pivots etc. so they will need updating during this process. The Excel template should just close down and remain in its original format.
This process will occur once a week so the dates will have to change accordingly.
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 have this UDF in my excel template that changes a set of numbers in to letters corresponding a code.
For example the code is "EUCHARISTO" it would simply mean
E=1, U=2, C=3, H=4, A=5, R=6, I=7, S=8, T=9, O=0 and by default the tenths position (".0")=X the hundredths position (.0"0") = Y and If a number repeats it becomes G.
Function LetterCode(ByVal Numbers As String, Letters As String) As String Dim X As Long Numbers = Format(Numbers, "0.00") * 100 Letters = UCase(Right(Letters, 1) & Left(Letters, Len(Letters) - 1)) If Numbers Like "*0" Then Mid(Numbers, Len(Numbers)) = "Y"
[Code] ...
Also, where best to put this code, In a query or in a module.
I have a few problems and I want them to be able to be done from switchboard:
1. Is there a way for me to export a particular report (after selecting it) to a closed excel template, that is formatted? It would open the excel template (that has a logo and column headings), export data to below the column headings, then save the file with a unique name?
2. Also, a way to import data from an excel file, after allowing the user to select file? Only data below the column headings mentioned above. Same data will be appended to existing table.
So I press a button on my Form1 and my tbl_customers table is exported onto a specific sheet in a templated Excel file "customer-template" that I have created.
This file has formulas on another sheet that based on the imported data.
The file is then saved to a specific location C:AccessCustomersHistory with the file name based on a date that was criteria from my original form E.g. "customers 11-02-15"
I have the code below which takes information from a form on access and sends it over to the correct place on an excel spreadsheet template. This works fine but I then need it to save and send on outlook.
The issue I am having is that the saved document is not attaching to the e-mail. The subject etc all work fine but the excel spreadsheet just doesn't attach. When I go into the folder I have specified for the document to be saved in it isn't there either. :0(
The code for the e-mail "callmail" function works perfectly for word documents but I don't know if it is different for an excel file.
Code:
Private Sub Command154_Click() On Error Resume Next Dim appExcel As Excel.Application Dim wbook As Excel.Workbook Dim wsheet As Excel.Worksheet Set appExcel = New Excel.Application
I'm not very experienced with Access. I'm looking for a very simple sample of how to export Query results to an Excel Template document. Especially to specific cell references. Would anyone have one to share. I'm trying to get my head around the concept first.
I may have to use Access 2000 and later versions to run the database, so do I have to cater for both DAO and ADO?
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 thought i would ask this question an how to import data from excel into access.
I am using sage to invoice clients and i can output this in to excel format then i would like the best and simplest way to pull the data though into access.
The data file has column headings at the first row ie A1 though to J1 and after this is the data, i have a table setup with the relivent table names, I had thought how easy this would be, but as always this was not the case.
The outputed excel sheet will vary in location so i think i need a diolog box to select the file and then some how link it to extract the data, is this right and if so how can it be achived.
Any thoughts
Alastair
UPDATE **************************************Resolved Thanks to Pat Hartman *************************
I have structured a subform as a datasheet to allow users to copy and paste from Excel into the database. Datasheet has numerous fields and 18 rows. I have set up recalculations of other data in the AfterUpdate event of the fields in the datasheet. This works perfectly until the user copies multiple rows from Excel and pastes them (one column at a time) into the datasheet. Is there an event I can capture when the user copies and pastes a column? Muchas-Grasias for any assistance.
Hello, I am here back in the forum because would like some help to define a code.
Meloncolly has already helped me but I think that I have mixed up two pieces of code and cannot find the solution.
I need to export my access data to a document in excel. I will select the recorset of the data to export, using a combo box on a form named MENU.
The excel file is a template named MASTER. Before the recorset is copied, I need to make a copy of the template via code. The copy will be the document into which copy the data. Once the data is copies, I would like an input box asking users if they would like to save the new excel file and be able to name it with the name they will type in the input box.
My last problem is that the database is used by 20 users and what happens if are all exporting to the template? Will this automatically name itselft, MASTER1, MASTER 2, etc?
This is the code that I am using and tried to put together. It does copy the data into master 1 but leaves the template opened. It also tells me that there is something missing.
THanks:
Private Sub ExportToExcel_Click() Dim myid Dim obj As Object Set obj = GetObject("C: estMaster.xls") 'Dim mypath
myid = Me.[MyCombo] 'grab the three field values from the table Dim mySSN, myFirstname, myLname mySSN = DLookup("[WESSN]", "[MASTER]", "[id]=" & myid) myFirstname = DLookup("[WEFN]", "[MASTER]", "[ID]=" & myid) myLname = DLookup("[WELN]", "[MASTER]", "[ID]=" & myid)
'open excel and the required file Dim appXL3 As Excel.Application Dim blnStartXL3 As Boolean
On Error Resume Next ' Check if Excel is already running Set appXL3 = GetObject(, "Excel.Application") If appXL3 Is Nothing Then blnStartXL3 = True 'Else ' We have to start Excel ourselves Set appXL3 = CreateObject("Excel.Application") If appXL3 Is Nothing Then MsgBox "Can't start Excel", vbExclamation GoTo exit_handler End If End If With appXL3 '.Visible = True On Error GoTo Err_Handler
What I have now is an excel template (with ~12 worksheets) that many regional offices use to enter in some lease data, from which the excel sheet creates a rental schedule and does a whole ton of calculations on that data. Some are NPV calculations, some are yields etc etc.Eventually, I would like to:
1) Enable users to fill out one of these templates, and save the data to the database (Just the inputs? All the data? My reading suggests just the inputs) 2) Use the database to produce one of these templates for any lease in our system (shouldn't be hard, from what I've read) 3) Sum up calculations from this template for many records (eg. if a tenant has many leases, what is the NPV of all of those leases, or what is the total NPV for all tenants)
I have the inputs (from Access) I will have no problem using them in the excel version, but does it make sense to use Access given that I may need to somehow be switching back and forth to get the info I want for my various reports? I am very comfortable writing macros in VBA for excel, so if that's the solution, that is no problem. I assume what I need for #1 and #2 is a macro to arrange the inputs from the excel sheet into a format that can easily be dropped into access tables and vice versa.
Does anyone know how to exclude the header information when copying a record from an Access table and pasting it into Excel. When I paste into Excel, it is also pasting the header information from Access which I do not want. I only want the data to be pasted.
How much formatting can be done to a report from Access into Excel? I am trying to (or will be in the next day or so) to create a report to export data and I would like it presented in a specific format. This is hopefully to replace a spreadsheet where someone currently has to collate and re-type a load of info that has already been typed into various other spreadsheets.
Access is going to happily cope with all of those users entering their data to a table, and I would like to be able to output that data to something similar to the end result now? Is there a way to have an Excel template file and simply direct Access what to put in which cell?
I would like to create a table containing email templates for use around various applications and interfaces. I would also like the user to be able to create the text for these templates, but as well as the dynamic text. So a user might create a record as such:
Email_Template_ID: 1234 Email_Name: "Payment notification" Email_Subject: "Project ID: {Project_ID} - Payment made" Email_Body: "Dear {Title} {Surname} We have recently made a payment of {Amount}. Please check your bank account in the next two days. This relates to Project ID: {Project_ID}, {Project Name}. Best wishes Payment Team"
So a user, with sufficient knowledge of certain field names, would be able to drop in the dynamic content as they deem appropriate. The VBA would then replace anything in curly brackets with the genuine field name.
I could possibly start with Replace(Email_Body, "{", "' & [") and repeat it for the end curly bracket, but I'm sure VBA would just render this as Dear ' & [Title] & ' etc. without interpreting it as Dear Mr...
I hope I've explained myself OK. To look at it another way, in Excel there is a function called INDIRECT() which allows Excel to interpret a cell reference as the actual content of the cell...
I'm using the below to merge an access form into a word template (it's a starter for 10).
That works fine. What I need to do is save the word template as HTML so I can then upload it to my website.
Dim wordApp1 As Word.Application Dim docPath1 As String Dim fileName1 As String Dim PageName As String PageName = Forms!Frm_Page_Create![Page] docPath1 = Application.CurrentProject.Path & "Merges"
I'm trying to paste the values of worksheet 1 by value. Please see the attached two files.
When I run the code, it indicates that variable ( xlpastevalues) is not defined.
It is not a variable....
Code: Private Sub Command0_Click() Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.Workbooks.Open "Your locationmetric_DEV.xls", True, False With xlApp.activeworkbook.worksheets("Metric summary") .usedrange.copy .range("A1").pasteSpecial paste:=xlPasteValues End With End Sub
The GetDates Command BUTTON should generate the red data above, like:
Private Sub GetDates_Click()
Go to a new record Go to the field App_Freq and fill it with: Copy/paste the data from the field of the same name in the previous record Go to the field App_Date and fill it with: data based on this calculation using the PREVIOUS record fields: App_Date + App_Freq (which are days) Go to the field Action_Date and fill it with: data based on this calculation: App_Date (of current record now filled out - step above), minus 7 (days). End Sub
That's it!
All the dates format is set to medium date. An error message should come up in case App_Freq is empty, for example
Also note that the data can be manually changed at any time and should not revert to its last calculation. The button only generate data into a new record.
