Dynamic Excel Queries
May 8, 2006
I've got a database that I need to import a vacation days table from an excel sheet. The biggest deal with it all is that the excel file & range it needs to search for the data is dynamic, actually I also need to be able to scan different sheets within the excel file.
Now, im not new to all this stuff, ive done all sorts of complicated excel manipulation in VBA as well as queries and custom processing, but I've been away from it all for roughly 8 mo. so im rusty.
If anyone has a suggestion about how i could go about loading the excel file (dynamically) and pulling information from it (i will be doing aggrigates like SUM & Count, however i can program these manually if need be.)
Thanks
-ReAn
View Replies
ADVERTISEMENT
Jan 21, 2014
I am building a database which tracks tasks assigned to employees. I want to have one of my forms displaying a view which shows at a glance which tasks are assigned to whom at what times of the day, with a click opening a task for editing. My current attempt is to create a crosstab query with the employees as the column headings ("dynamic crosstab" query) and a table of time values for the hours of the day providing the row headings, with the task as the value.
I then created a form based on the query with Datasheet view as the default view (apparently necessary to set properties/event handlers to query values. I got the idea from the Northwind sample database) However, the form fields do not update to reflect changes to the employee list and therefore the onClick handlers are not applied to newly added employees. What to do, besides for programmatically setting the event handlers?
View 2 Replies
View Related
Mar 3, 2006
i have a database that imports files with dynamic amounts of fields, runs querys on the data and outputs it to a speadsheet. The The problem is instead of making it run querys on each column, i have it running on them all collectively. Now i want to change it so a query is run on each column then output to its own spreadsheet. Does anyone know how to do this?
View 1 Replies
View Related
Jun 19, 2006
I have several fields that have a drop down list. Say the Field is called "Age" and I have choices in the drop down menu as 1, 2, 3, and 4. How do I create a query that would accept input from the user to show 2 of the 4 or 1 of the 4 or all of the choices using one query that has checkboxes, and possibly using a form to do so. I have a form that has a list of all entries. Now I just need to make it able to filter based on user input.
Say I have checkboxes for 1, 2, 3, 4, and all choices. Can a list filter only what I check? Can I create a form that would have all the choices on it and just click a button that says "submit" and have it filter, or print the form. How do you get the checkboxes to filter when checked, but not when unchecked? Where does Access store the true/false, on/off, yes/no responces that come from the checkbox? Is there a variable that gets set to a 1 or a 0 based on the status of the box?
View 1 Replies
View Related
Jun 30, 2006
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
View 6 Replies
View Related
Jan 20, 2015
I have a relatively small data base.
There are a number of what I call reference tables. These are fairly static and in the main will not change.
There are a further half a dozen tables all linked together.
The system basically takes an enquiry which may lead to a repair taking place. The repair will have a number of jobs and each job may have parts replaced.
What I want to do is have an identical database which to begin with will have exact copies of all the reference tables. The remaining half a dozen tables initially will be empty but grow with time.
The user wants to be able to copy the completed repairs he selects over to the 2nd database (which is initially empty). As weeks go by he wants to select further completed jobs that have not already been copied over to the 2nd database. The 2nd database will be updated so I can't just copy over all completed repairs each time.
I had envisage him doing this by using a front end to the 2 databases and firstly selecting which of the completed jobs which haven't been copied over. This is a fairly straight forward query that will update a field on the repair table.
Once he has done this I was thinking that I could run a query that would pull all the rows from the 6 tables from database 1 and update the corresponding tables/rows on the 2nd database.
I don't actually know how to approach that. The process needs to be simple for the user. ie 1. select repairs to copy over 2. run something to copy them over.
View 11 Replies
View Related
Dec 17, 2013
Is it possible to have the user enter the number of top records to choose in a query?
I know that you can enter a number or percentage in the Top Values property of the query but I would like the user to be able to enter this number each time they run the query since it may be different each time and not always, say, the top 10 records.
View 12 Replies
View Related
Jul 10, 2013
I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.
If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.
Code:
DSum("[invquan]","[qrySalesByStockCode]","[stcode] = '" & [stockcode] & "' and [Month] = Month(DateAdd("m",-13,(Date())))'")
View 1 Replies
View Related
Oct 18, 2013
Sometimes i have a problem with dynamical queries. For some of these i need to put in the same parameter value two times, before it works. Why this occurs?
View 2 Replies
View Related
Jan 22, 2014
We have a ComboBox on a form with the months: Jan, Feb, Mar, Apr, etc.
We have fields in a budget table named: Jan, Feb, Mar, Apr, etc.
We want to create a query that pulls the correct field based on the value in the combo box.
This is what I have that doesn't work:
MyField: [Query1].[SumOf] & Forms![Main Menu]![test]
How to do this?
View 4 Replies
View Related
Apr 23, 2015
I have a dynamic cross tab query - thus the column headings will change each time it is run.
At present the column headings are displayed in alphabetical order - how can I change this so they are based on a different order - eg by the descriptions corresponding ID
View 3 Replies
View Related
Jul 3, 2014
I'm trying to run a query which fetches only the last 2 years of data for a given region from a table with several years worth of data (there are year, region, sector and rank columns among others). The region is passed into the query from a combobox from Form1.
The first problem was that some regions have up-to-date data and some not so much e.g. for Europe the "last 2 years" mean 2012,2013 for Asia its 2011,2012. In order to deal with this I've created a crosstab query which works well except for one thing - because the columns are dynamic (dependant on the region) the column headings change as well.
And here comes my question, how can i fix the column names to be e.g."Current Year" and "Prior Year" independent of the query fetching 2012,2013 or 2009,2010? I've tied different things with PIVOT... IN ... but with no luck.
Here's the sql for the query:
Code:
PARAMETERS [Forms]![Form1]![cmbRegion] Text ( 255 );
TRANSFORM min(DataTable.Rank)
SELECT DataTable.Region, DataTable.RegionalSector,
FROM DataTable
WHERE (((DataTable.Region)=Forms!Form1!cmbRegion))
[Code] ....
View 7 Replies
View Related
Jul 18, 2013
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
View 1 Replies
View Related
Aug 16, 2007
Ok, here I go. I have seen several examples on here, but still can't seem to figure how to get this to work for me. Currently the code below exports qry1 to excel with no problem. I have 4 other queries "qry2, qry3,qry4,qry5" that I need to export to the same excel workbook but in different tabs for each. How do I change the below code for this work? Can someone shed some light on this?
Option Compare Database
Option Explicit
Private Sub cmdExportAutomation_Click()
On Error GoTo err_Handler
MsgBox ExportRequest, vbInformation, "Finished"
Application.FollowHyperlink CurrentProject.Path & "AoOutput.xls"
exit_Here:
Exit Sub
err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub
Public Function ExportRequest() As String
On Error GoTo err_Handler
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSql As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Const cTabOne As Byte = 1
Const cStartRow As Byte = 2
Const cStartColumn As Byte = 1
DoCmd.Hourglass True
' set to break on all errors
Application.SetOption "Error Trapping", 0
' start with a clean file built from the template file
sTemplate = CurrentProject.Path & "AOTemplate.xls"
sOutput = CurrentProject.Path & "AoOutput.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabOne)
sSql = "select * from qry1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSql, dbOpenSnapshot)
If Not rst.BOF Then rst.MoveFirst
' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to AoOutput.xls"
Me.Repaint
For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)
If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If
wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next
wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop
ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."
exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function
err_Handler:
ExportRequest = Err.Description
Me.lblMsg.Caption = Err.Description
Resume exit_Here
End Function
View 5 Replies
View Related
Aug 17, 2015
i have a query in ms access..i want to makew an excel file from that and then send it via email.
View 10 Replies
View Related
Nov 20, 2014
I need to import the data from an Excel spreadsheet but only certain cells. Is there any way I can call out a range of cells in a query and tell it what fields in a table to put those records in?
View 7 Replies
View Related
Nov 4, 2013
I haven't used Access in a few years and am trying to replace an Excel spreadsheet with Access.One field in Excel looks at the prior record, if they are the same it doesn't update the current record. The excel command is: If($Y3 = $Y2, 0, sumif($y:$Y, $Y3, G:G)). Is there any easy way to do this with a query?
View 1 Replies
View Related
Nov 6, 2013
I have got the following code
On Error GoTo ErrorHandler
Dim exApp As Excel.Application
Dim exDoc As Excel.Workbook
Dim exSheet As Excel.Worksheet
Dim Dateiname As String
Dim SQL As String
[Code] ....
I'll get the following error:
error message 1004: can not give a sheet, the same name of the sheet
View 7 Replies
View Related
Aug 28, 2014
I have a problem, so I am trying to append data from excel which works fine but I want to append it to the next empty available row in the datasheet view. At the moment it is just appending it to the top, first line. Im using MS Access 2007-2010.
how to do this?
View 6 Replies
View Related
Jul 10, 2013
I wrote a SQL query that works fine. But when I call a custom function to export it to Excel, my query SQL will get deleted (not consistent). All that is left of the script is SELECT;
MS Access 2007, Windows 7.
Here's how I call the function:
Code:
Private Sub exportQryCustStatusAnalysisLite_Click()
Call ExcelSmsOutput("Query", "qry_custStatusAnalysisExport", "qry_custStatusAnalysisExport", Forms![frm_main].[filePath])
End Sub
Here's the custom function:
Code:
Public Function ExcelSmsOutput(objectType As Variant, prefixFileName As Variant, objectName As Variant, filePath As Variant)
Dim outputFileName As String
outputFileName = prefixFileName & " " & Format(Now(), "yyyy-mm-dd hhh mmm sss")
[Code] .....
View 2 Replies
View Related
Jan 20, 2014
I am trying to export a table from access to excel. It has a couple of columns with times in and when excel opens it just shows 00/01/1900 for all the values.
Here is my query that creates the table for exporting:
Code:
SELECT tblRoadClosuresAdditionalDates.Reference, tblRoadClosuresAdditionalDates.DateOfClosure, CStr(Format([TimeFrom],"Medium Time")) AS TimeFromMod, TimeValue([TimeTo]) AS
TimeToMod, tblRoadClosuresAdditionalDates.NumberOfPeople INTO tmpAdditionDates
FROM tblRoadClosuresAdditionalDates
WHERE (((tblRoadClosuresAdditionalDates.Reference)=[Forms]![frmProgressRoadClosures]![sfrmRoadClosuresAdditionalDates].[Form]![Reference]));
You can see a couple of my attempts to handle this data above.
I am using this code to create my excel table.
Code:
DoCmd.OpenQuery "qryRCADditionalDatesFilteredExport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tmpAdditionDates", "xxxxxxxxxxxxxx", No
way to handle this field so that I get just the time in the medium time format appearing in the excel table.
View 4 Replies
View Related
Feb 25, 2014
I have been trying to understand which method to use for looping through excel cells and storing these into access tables. However, I am having difficulty with so little knowledge in vba.
I have lets say two tables (rows for each table are not fixed) in a worksheet and I want to loop through these rows and store each tables (PROJECT PLAN 1 and PROJECT PLAN 2) in a separate table in access.how to loop through the PROJECT PLAN 1 and PROJECT PLAN 2 in excel and store these in table1 and table2 in access
I have these in excel
B3 I have "PROJECT PLAN 1"
B4 COMPANY | C4 DESCRIPTION | D4 TIME
B5 Google | C5 aaa | D5 10
B6 Microsoft | C6 bbb | D6 11
B7 IBM | C7 ccc | D7 12
next row 8 is blank row and columns
B9 I have "PROJECT PLAN 2"
B10 COMPANY | C10 DESCRIPTION | D10 TIME
B11 Google | C11 aaa | D11 10
B12 Microsoft | C12 bbb | D12 11
B13 B14(merge cell) IBM | C13 ccc | D13 12
B13 B14(merge cell) IBM | C14 ccc | D14 12
This is the code I have so far:
Code:
Dim objXL As Object
Dim xlSht As Object
Dim xlWB As Object
[code]....
View 6 Replies
View Related
Nov 18, 2014
I'm trying to automate a repeated data import event. Here's the user process I'm aiming for:
We have a handheld barcode scanner which will be used to enter orders into an excel spreadsheet with the following fields:
PatrolID, UsedDate, ItemsID, NumSignsOut
After scanning in an order, the user will plug the scanner into a computer and download the spreadsheet to a predetermined file location/name.
The user then opens Access and pushes the 'Process Order' button which imports the excel sheet and generates an invoice.
I already have the invoice process working, I'm now trying to get the data import to work. Here's the table structure:
tblSignUsed:
UsedID, PatrolID, UsedDate
tblSignUsedDetail
UsedDetID, UsedID, ItemsID, NumSignsOut
tblSignUsed Joins tblSignUsedDetail ON UsedID = UsedID
tblSignUsedDetail is the line items, tblSignUsed is the orders. Therefore, the import function has to first take the first two columns to generate an order in tblSignUsed and then take the second two columns to generate order details associated with the order which was just created.
View 8 Replies
View Related
May 14, 2015
I'm attempting to export some table queries to excel using the code I have posted below as a module.I am getting an error saying it cannot find my query.I have used this code to do the above on 2 queries within the same table and it works fine. I don't understand why it would not work for a different query.
Option Compare Database
Private Sub exportQueryToExcel()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CUSTOMER FOCUS", "C:Usersgareth.davies1DesktopTrainingSHAREPOI NT FEED MASTER.XLSM", True
End Sub
View 4 Replies
View Related
Jul 17, 2014
I have been trying to write a macro that will do the following:
- Look to a specific folder in my home drive (nb this may change)
- select all of the excel files that are in that folder
- select various cells in each of those spreadsheets - each spreadsheet is formatted the same with the same structure. The cells are random, e.g. D6, I22, H4, K4, D17, so I cannot select a whole range
- copy these cells and paste them into one row of a database
View 3 Replies
View Related
Apr 24, 2008
I am an access (2003) amateur, willing to generate an excel file or table from the data from many queries. Moreover, some fields in those queries having different names but same data type, should be filled in the same columns in the new excel/table. Should I use Macro/SQL?:(
View 2 Replies
View Related
