I have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.
View 2 RepliesI need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
View 8 Replies View RelatedI am not sure how to setup the initial connection to the datbase from excel and was wondering what would be the best way to go about things. Is it possible to run the query stored in the database and pass it a value or would I be better off just coding it into a sqlstring within the macro.
Here is the query that is in the database...when run it asks for the "Status?" variable.
SELECT ordernumber, mobilenumber
FROM bookings
WHERE status = [status?];
So yes, really simple query but yeah just want to know how to pass it that variable from excel or can I just set it up in the actual macro to maybe read the status from a cell?
I'm creating a master spreadsheet and I want it to pull live data from other spreadsheets depending on a variable.
I've a cell that has a variable date. I've files saved that have this variable attached to it in the name format: 'filename variable'.
I want to pull data, hopefully live, from workbooks based on this variable.
For example, I have files: generic 01212014, generic 01222014, generic 01232014.
The date is stored in the master workbook say in A1 that I can manipulate by entering a different date to pull data from a different workbook.
='[generic 01212014.xlsm]Database'!$L$3
='[generic 01222014.xlsm]Database'!$L$3
='[generic 01232014.xlsm]Database'!$L$3
There has to be away to make the bolded portions of the file name variable, right?
I have to import data into Excel from Access (yes, I have to import it to Excel and cannot use Access exclusively). When I import; however, the formatting does not come over correctly, most especially losing the formatting of numbers. I need one of two solutions:
1.) How to get the formatting to move over correctly?
2.) Or how I can save the formatting of a table, then be able to apply it as a "saved" format? The biggest thing is that the numbers will changed the number of decimal places and/or change from general numbers when I want to see them as currency.
The top table shows how i want this formatted, the bottom shows how it is importing.
I have a crosstab query in Access that groups by month and year. In Excel 2003, I have a query that pulls in the crosstab query.
At the time, I couldn't just pull in the Access table. For the other Access tables and queries, I could go to Data >> Import External Data >> Import Data >> select the database as a source >> select the table name.
However, to get the crosstab query, I had to go to Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select the data I wanted.
Since I wanted all the data, there did not seem to be a difference. But now that new dates are being added to the database, there *is* a problem. When I wrote the query, I only included the months available -- up to June 08. Now that there is data in July 08, that month exists as a heading in the crosstab, but Excel's query doesn't know to pull it! I have to manually edit the query to add the new month.
So my question is... is there a way to do this automatically? I have two crosstabs in five different spreadsheets, making a total of ten manual updates I have to remember to do each month.
I run a web query to populate a table in my spreadsheet. The data is dynamic - and on some days, I will get a couple of extra rows in the table (its weather data, so e.g. when its raining, they will forecast an extra row of how many mm, etc...)
To give a simple example, right now I use Hlookup to locate the value of F3 (the date) in my dynamic range of B26:G26 and step down 3 rows, where my needed temperature values are for that corresponding day. This works great like this already: =HLOOKUP(F3,B26:G29,3,FALSE)
The big issue is...what happens if my target values are not always 3 rows down? If the query refreshes with any extra rows, this reference is shifted. (Well, my starting "Anchor" is shifted)
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................
I have been trying to pass a query to fetch data from Access database.
The query is:
sSQL = "Select Client+, Entry-ID, Type of Contact, Manual Creation, Date Created, Transfer-date, Respond SLA FROM BASE DATA"
The problem I am facing is in:
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText
The error I am getting is:
Run-Time error '-2147........'
Method 'Open' of Object '_Recordset' failed...
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
I have an excel file that imports data from an access database. Now the user wants the database password protected I edited my code to include the line "user ID=admin;password=risk;" but I'm getting an error message stating: "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."
I'm bothered because I did not create a workgroup information file. I just want a password on the database I did not create users and groups.
Note this code works fine without the password protection and the added line.
My
Sub Update_Click()
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C: est.mdb;" & _
"user ID=admin;password=risk;"
szSQL = "SELECT * from data"
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
I have an Access table which has following fields:
ScanDate Number DataType
Type Number
Type1 Number
Type2 Number
BatchNo Number
Cases Number
Pages Number
Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.
See attached the Excelsheet where the data should be populated to. WBCount.xls
I've about 10 pivot tables in Excel that are populated via an External Query link to Access. In each case I have pre-written views with all the columns I need so that the query can just pick them up. Nine of the ten queries work perfectly, but one doesn't - reporting "Too Few Parameters - Expected 1" when I try and click on the "Return Data to Excel" in the import wizard.
I can see the column headers in Excel to select; so I'm assuming that it can parse the SQL but no results are being returned. Just to confirm though - results are returned (>8000 of them) in Access.
SQL code below:
Code:
SELECT z.pathdurationhours AS PathHours, Count(z.pathdurationhours) AS Paths,
Count(z.pathdurationhours)/(select count(*) from (select [activity date],
[conversion ID], count(*) from tbl_dfa_p2cdata group by [activity date], [conversion ID]) ) AS PCT
FROM (SELECT a.[activity date], a.[conversion ID], val( max(a.[interaction time])-min(a.[interaction time]) )
*24 AS PathDurationHours FROM tbl_dfa_P2CData AS a WHERE a.[interaction time]
"" GROUP BY a.[activity date], a.[conversion ID]) AS z
GROUP BY z.pathdurationhours;
I use Office 2010. I have an Access database that goes out and runs various queries from our systems of record. It then takes the data and dumps it into an Excel file. I have taken the info and created a file the grabs the raw data, converts it into Pivots and then Charts. All info is automatically updated however the size will change depending on what department is quering the data. It is really all done with a click of a button. I have tried to merely uncheck the "(blank)" but it comes back everytime the report is ran.
How can I keep it from returning??
Our spreadsheet pulls values from a column when a month is inserted in a certain cell:
OCT is typed in B2 and in the column below it, from B10 down to B286, data (numbers) are pulled from the October column K10 through K286 using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed in B2 then the same happens except data is pulled from the November column, L. The monthly columns from K through V are tied to and updated
from other sheets. As you can see the problem is that nested functions allow only 7 and I need 12, one for each month. I have looked at the VLOOPUP and the HLOOKUP but our data is not set up that way (tables) since they need to be exact numbers pulled from the monthly columns that are tied to other sheets.
OCT (B2)
OCT NOV DEC
Complaint 3 (B10) 3
6
Inspection 10 (B11)
10 2
NOV 11 (etc)
11 5
Door Notice 1 and
so on
Recheck 32
Citation 2
Work Order0
Demolition 0
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
I'm in need of serious help, extremely new.
I have created a userform in excel which I want to transfer data over to access with.
I have built a command button to send data to access code below.
Private Sub CommandButton2_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=filepath.mdb"
Set rs = New ADODB.Recordset
rs.Open "Main", cn, adOpenKeyset, adLockOptimistic
With rs
.AddNew
.Fields("Status") = ComboBox1.Value
.Fields("RRR") = ComboBox46.Value
.Fields("RRS") = ComboBox52.Value
.Fields("SRR") = ComboBox47.Value
.Fields("SRS") = ComboBox53.Value
.Fields("WSR") = ComboBox48.Value
.Fields("WSS") = ComboBox108.Value
.Fields("WPR") = ComboBox110.Value
.Fields("WPS") = ComboBox112.Value
.Fields("WER") = ComboBox49.Value
.Fields("WES") = ComboBox54.Value
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
This works fine when all comboboxes and textboxes have entries in but doesn't work when the have been left blank and there will be occasions when they can be left blank.
I am not able to retrieve the date from Access table.Here is below code.. How to retrieve the data from ACCESS..
Dim adoCN As ADODB.Connection
Dim strSQL As String
Const DatabasePath As String = "U:workareaNew Projectdb1.mdb"
'Function argument descriptions
'LookupFieldName - the field you wish to search
[Code] ..........
I need a formula for the highlighted yellow area that pulls and sums from a reference table. Not a traditional Vlookup because the reference within the report can appear in multiple columns within multiple rows.
ReportABCDEF1AccountJanFebMarApr2Overtime(L7)Overtime300 300 300 300 3
Salaries(L8)Salaries1,170 1,170 1,170 1,170
Expected ResultsReference PageABCDEF1
Account 1Account 2JanFebMarApr2
Additional PayOvertime(L7)1251251251253
Additional PayOvertime(L7)1751751751754
Salaries(L8)Ex Staff2252252252255Salaries(L8)
Ex Staff3253253253256Salaries(L8)Ex Staff4104104104107Salaries(L8)
NE Saff1101101101108Salaries(L8)NE Saff100100100100
I'm trying to build a query which matches two tables which say has a number like A#### , I want the query to give me the A number's which do not belong in the second table but which do in the first table. i believe i need an SQL query to do this?
View 2 Replies View RelatedI need to populate data from Access table to sheet1 using Excel/vba. I want to find out the percentage of batches scanned for the date selected for each time slot. There are Time slots in the columns from Q to X. i.e 8:00,9:00,10:00,11:00.....etc.
so I need to find out percentage of batches scanned before the above time slots.. To count total number of batches , we will use count(BatchNo) function in the SQL query. Because of company restrictions, I can't attach the access database but to give u an idea how the database is stored, I copied the data in sheet2 of the attached workbook. We have to find percentage of batches scanned in sheet1.
I put together a little table for one of my LAN based facilities to track their Overtime Savings as compared to each of their individual units. It works great.
What I would like to do is to export the data on a weekly basis to Access. I set up an access table to recieve the data and I can get it all to work just fine except I want the users to just click on a menu item and have the export take place in the background.
I know how to create on the fly menus and get them to accecpt commands from Excel Macros so that is not an issue.
I know how to share data in Excel from Workbook to workbook, closed or open. I'm sure it can be done Excel to Access I just can not for the life of me find anything to jumpstart me in the right direction in either application's help files. Anybody out there linking their Excel data to Access?
A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
I use web query to get some range of data. This data is refresing and changing on some time (i set query refresh on 1 minute). So, i'd like to fetch this data and make the table with all data (new data on eg. every 1 minute). How to do this?
I use Excel 2003.
i am trying to use VBA to query a access database that i have i want to be able to pull the information that matchs either textbox4 or 5 i am using the code below but i am getting global errors.
Private Sub CommandButton1_Click()
Dim custname As Variant
Dim Custnum As Variant
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
custname = Range("TextBox5").Text
Custnum = Range("TextBox4").Text
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=Insolvency;Description=Insolvency;APP=Microsoft Office XP;DATABASE=Insolvency;Trusted_Connection=YES"), Destination:=Range("Listbox1"))
.CommandText = Array("SELECT * FROM POST WHERE Customer_Account_Name=" & custname & " AND Customer_Account_Number = '" & Custnum & "' ORDER BY Customer_Account_Number")
.Name = "Insolvency Post Query"..........................
An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?
View 3 Replies View RelatedI have a spreadsheet and an Access database that contain the same items and prices. The database and spreadsheet are on a server shared drive. I would like the spreadsheet to automatically update its list of items and prices from the Access database whenever the spreadsheet is opened. Right now, we update the item and price list on both the spreadsheet and the database but I want to just update the database and have the spreadsheet pull the updates from the database.
View 2 Replies View RelatedI am looking at a way to create a dsitributable excel spreadsheet (.xlsx), which contains non-sensitive, yet restrictied organisational information segmented by regional department for NCSA, EMEA for APAC respectively.
By design, all data has been collected in a datatable on a password protected "data" sheet - and - presented on an "executive summary" sheet. T
The "executive summary" sheet contains a privottable with select "multiple options" to allow for aggregated dataviews on executive level. However, on a regional department level, the users must only see their own regions, hence the filtering mechanims on the pivottable should be restricted.
Currently, the restrictions on the pivot tables are made in VBA, using the following script. However, it still allows people to change the "multiple options" filter in the pivot. How can I restrcit this option
Sub RestrictPivotTable()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = True
.EnableFieldList = True
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
For Each pf In .PageFields
[code].....
As a sidenote; the above design requires that a spreadsheet is compiled for each region each month. Ideally, the ability of filtering, could be password restricted - or - filteren. So only one selection would be presented for the NCSA, EMEA and APAC respectively, while Executives could view across one-or-many regions? However, as Pivot tables are used by design, I cannot see how this could efficiently be achieved?