Query SQL 2005 Views With Excel
May 20, 2009
I've posted this on Experts Exchange too, specifically trying to target the SQL Server experts. http://www.experts-exchange.com/Soft..._24423637.html
SQL Server isn't really my thing. I have been asked to quote on a job to build some models. One of the components is to pass parameters to sql server views and return the data into a table in Excel. I never done this.
1. Is it possible to pass parametrs to a view thru Excel?
2. Will the view execute based on the users permissions (different users using the Excel template have different access rights)?
3. Any other things to consider?
I don't need specific solution, only really some broad advice. I really only want to know how feasible this is...
View 9 Replies
ADVERTISEMENT
Nov 26, 2012
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.
View 3 Replies
View Related
Jul 11, 2009
I have a column that is updated several times a day, I need to be able to view that list in a userform combo box and that box to be able to react like an autofilter list. I've tried the advanced filter thing but as th elist is contantly updated it makes this very hard to handle as I have to set macros to compile the list and then delete it once it's been used. Is there a way I can set vba code so the ComboBox populates unique fields, sorted AZ and removed the blanks without using the advanced filter..?
View 7 Replies
View Related
May 20, 2013
Is there a way to have 2 views of the same sheet that will enable either user to only see their own requirements (columns, row)s but that will update the overall worksheet. The users would not be accessing simultaneously. Complex sheet so just need to have user access to a subset of data.
View 1 Replies
View Related
May 11, 2008
I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot.
View 9 Replies
View Related
Nov 22, 2006
Right now, I've this pop up box which prompts users for passwords and user name for log in. They are two main users : Admin and User. Admin is the user which is allowed to see all sheets. While User is only restricted to user interface sheets.
The problem is for the "User" I want to restrict a view in the sheet
"ELEMENT". I wish to hide columns L to AI if "User" logs in. But if "Admin" logs in, I want no columns hidden. Everything must be visible.
Private Sub CommandButton1_Click()
Sheets("Main").Select
Dim strUser As String, strPword As String, strWs As String
Dim w As Worksheet, c As Range, r As Range
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value
Select Case strUser
Case "User"
If strPword = "User" Then
Sheets("SMXINVENTORY").Visible = xlSheetVisible
Sheets("SMVINVENTORY").Visible = xlSheetVisible
Sheets("SMIINVENTORY").Visible = xlSheetVisible
Sheets("SMF1INVENTORY").Visible = xlSheetVisible..............
View 3 Replies
View Related
Apr 19, 2007
I'm dealing with a spreadsheet that uses custom views. Before I do anything with it, I want to make sure that there is no custom view enabled (e.g. just display all). I've tried Chip Pearson's ThisWorkbook.CustomViews("View1").Show suggestion, but it just throws "Runtime Error 5: Invalid procedure call or argument". My actual code is: ThisWorkbook.CustomViews("All (Est)").Show
View 7 Replies
View Related
Jun 8, 2007
I setup custom views for my worksheet
I dragged the custom view command to my menu bar
Everything works great on my machine
But I would like to send to other users and for them to be able to use the custom views w/o having to help them setup on the menu bar
Plus I've had problems in past where custom view tool is not persistent on the menu bar
Sometimes there, sometimes not
Is there a way to add to say cell A1 on the worksheet instead?
View 3 Replies
View Related
Jan 18, 2013
I have a worksheet set up with a number of pivot tables to show job summaries for each project manager. I created buttons labeled with each project manager that trigger a macro to filter the results to only show jobs for that specific project manager. There's one button for each project manager.
I'd like to create a macro that will essentially show each project manager's report and compile the result into one PDF. I only have 5 project managers so I don't need an array or anything if that makes it easier, I could write the code manually for each one.
So basically, it would do this:
1. Run macro to filter results to only show Project Manager #1 jobs.
2. "temporarily" print to PDF.
3. Run macro to filter results to only show Project Manager #2 jobs.
4. "temporarily" print to PDF.
5. Run Macro for Project Manager #3, etc.....
...............
Final step. Compile all the "temporary" pdfs into one file.
View 2 Replies
View Related
Aug 13, 2014
I have created a report with 22 sheets as different pages to be included or hidden depending upon the type of report needed. I had found several sources saying to use the Custom Views function. So for example I will unhide all the sheets and add as a Custom View "All Sheets" and then whittle it down to the 5 I might need for B type inspections and add that view as B Inspection. But when I try and toggle between them I'm getting an error message that says "Some view settings could not be applied" - and thew saved view I was looking for is not the same sheetwise.
View 1 Replies
View Related
Oct 6, 2009
1. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?
2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?
View 10 Replies
View Related
Sep 14, 2012
I am trying to use a checkbox to control the custom views that i have set ("Normal", "Hide") However after i entered it in VBA, it just does not work. After i check or uncheck the checkbox, it just keep going back to the "Normal" view.
Sub CheckBox1_Click()
If CheckBox1 = True Then
ActiveWorkbook.CustomViews("Hide").Show
Else
ActiveWorkbook.CustomViews("Normal").Show
End If
End Sub
View 5 Replies
View Related
Nov 4, 2006
I copied below code from one of Andy Pope's thread answers. Thanks Andy.
Private Sub CommandButton1_Click()
Dim vntFile As Variant
vntFile = Application. GetOpenFilename("Graphics Files (*.bmp; *.gif; *.jpg; *.jpeg),*.bmp;*.gif;*.jpg;*.jpeg," & _
"All File (*.*), *.*", Title:="Select Picture")
If vntFile <> False Then
ActiveSheet. Cells(27, 1).Value = vntFile
Image1.Picture = LoadPicture(vntFile)
Image1.PictureSizeMode = fmPictureSizeModeStretch
End If
End Sub
I would like to force the box to open in Views - Preview instead of List or Details or whatever it is at. I tried the following (and a bunch of other things), but I could not get it to work
With FileDialog
.InitialView = msoFileDialogViewPreview
End With
View 9 Replies
View Related
Aug 26, 2006
I have a large spreadsheet that I am managing and there are many users who access this for information. Because of this, we have all created "custom views" for our departments.
I continue to struggle with how to save changes in these custom views. Yesterday, I added and moved some columns to the spreadsheet and now all the custom views are messed up. The only way we have been able to work through this is deleting and creating new views.
View 9 Replies
View Related
Jun 12, 2008
I have a spreadsheet with four columns of data for every month (i.e. January has a Prior Year, Budget, Outlook, and Actual column). I have been trying to come up with a macro or form that will allow me to customize which columns I would like to see and hide the rest. For example, if I chose to see the Budget and Actual columns, it would hide the Prior Year and Outlook columns for each month.
View 2 Replies
View Related
Jun 30, 2014
I was trying to use IF and AND conditions to calculate values for a Col. but when I attempt to sum.
It appears that the value is always "0". Below is my test data.
Col. H Col. J
Qty Result
Row 713 0 <-Excel Formula: =IF(AND(H7>20,H7<=50),"1","0")
Row 825 1 <-Excel Formula: =IF(AND(H8>20,H8<=50),"1","0")
Row 960 0 <-Excel Formula: =IF(AND(H9>20,H9<=50),"1","0")
Row 10Total 0 <-Excel Formula: =SUBTOTAL(9,I7:I9) / =SUM(I7:I9)
View 3 Replies
View Related
Feb 16, 2012
I've inherited an SQL server database which imports data from a multitude of spreadsheets each night. Unfortunately the previous data is not deleted from the database or spreadsheets, so there are duplicated records throughout the database. The method they have been using to get data out is to "select distinct", which gets around the problem (not my preferred solution).
I now need to produce an invoice from the database, so I select distinct records for the previous month (based on date) from, say, the TRUCKING table into a TEMP table, so I can do my calculations from there.
But there are a couple of columns which prevent me from getting only unique records. They are the unique ID column and the TIMESTAMP column they have added.
If I try to select all the individual columns, except for the ID and TIMESTAMP, the string length goes over 256 chars and the query truncates.
So I tried "SELECT DISTINCT * FROM TRUCKING WHERE etc etc" but I get the ID and TIMESTAMP fields, which defeats the purpose, and I get duplicate records.
Is there a way to say, for instance, SELECT ALL EXCEPT the fields I don't want?
View 9 Replies
View Related
Mar 12, 2012
I have the below code that will do a data dump of an access table into excel based on an SQL query, is there a way to do a select statement that can match against the data in column C on the spreadsheet.
like:
"Select * From Bsmart where Serial_Number = ##[EXCEL COLUMN C1:Lastrow]##
Code:
Dim bkNew As Workbook
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String
Dim SQl1, SQL2, SQL3, SQL4, SQL5, varSQl As String
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
[Code] .....
View 1 Replies
View Related
Jun 6, 2012
I am using below code to delete the sheet from workbook.
For iTemp1 = Workbooks.Item(funEach.Name).Sheets.Count To 1 Step -1
Sheets(iTemp1).Select
If UCase(Sheets(iTemp1).Name) "TEMPLATE" And UCase(Sheets(iTemp1).Name) "WORKING" Then
Sheets(iTemp1).Delete
End If
Next
How can i avoid the below popup..
-Data may exist in the sheet(s) selected for delettion. To permanently delete the data press delete.
View 2 Replies
View Related
Jan 29, 2008
I have an access database that has many queries and in order to speed it up I have a Macro in access that runs it in no time, but I need to be able to run the query in access via a trigger in Excel and have come up with the below, but it's not working.
View 9 Replies
View Related
Jul 25, 2008
I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?
View 9 Replies
View Related
Apr 8, 2014
Using Excel 2013. Is it possible to delete some records from Sheet1 if the records exist on Sheet2?
I tried several SQL variations in MSQuery but all come back with error messages.
I tried:
[Code].....
View 3 Replies
View Related
Jan 22, 2013
We have a query, that looks at an external data source (db2 i believe). It will pull all data under a certain product set, and as an additional Criteria we currently have Serial Number.
Now, if the value entered is the serial number, lets say '1234567' for example, it will happily pull all instances of that into the table.
However, what I would like is a cell where I can enter the serial number, and the query will look at the Criteria, the Value will reference a cell (where we can type any serial number), and it will then populate it! I thought that would be fairly intuitive even for a complete novice like myself - now I am likely to have nightmares about Function Errors!
What I have tried:
*Setting the value to a cell ref... ie instead of '1234567' i have set the value to =$a$1 and in that cell i have a serial number.
*Tried to create a parameter. So value of [Serial Number] I then get a prompt saying "Enter parameter Value (serial number) - Regardless of if i type a cell reference (ie =$a$1, or i input the actual serial that worked before when it was '1234567' I still get a function error!
I keep trying different permutations of things, but I am ill prepared.
View 1 Replies
View Related
Feb 10, 2013
I am a teacher and have a recurring problem when trying to sort data in excel. The problem involves national curriculum sub levels. For those not familiar with these levels, there is a main level then 3 sub levels. The problem is that they are counter intuative to the way excel sorts data. The sub levels work like this:
4a - highest
4b
4c
3a
3b
3c
2a
2b
2c - lowest
There are lower and higher levels, but you get the idea. If I have a set of pupil data that I want to sort into decending order, I find it difficult in Excel. E.g.
Jacob2c
Mary3b
John2a
Sally2b
Sarah2b
Joe3a
Bill3a
If I manually sort the above data into descending order of national curriculum level it will look like this:
Joe3a
Bill3a
Mary3b
John2a
Sally2b
Sarah2b
Jacob2c
but if I try to get excel to do it, obviously excel just treat the levels as alpha numeric and sorts them like this:
Mary3b
Joe3a
Bill3a
Jacob2c
Sally2b
Sarah2b
John2a
I know there must be an easy way involving creating a custom list, but I don't know how to do it.
Problem 2
A related query involves finding a way to get excel to complete the data in the Sub levels progress required column below:
NameLevelTargetSub levels progress required
Mary3b4a
Joe3a4a
Bill3a4a
Jacob2c2a
Sally2b3c
Sarah2b3c
John2a3a
At the moment I manually complete this. E.g. for Mary the Sub levels progress required would be 4, as there are 4 sub levels between 3b and 4a. For Joe it would be 3 as there are 3 sub levels between 3a and 4a, etc. Is there a quick way to get excel to calculate this.
View 6 Replies
View Related
Jun 23, 2013
I am trying to get data in excel sheet using web query from the following web link:
But I the data returned is a 'hypen' (i.e. a dash symbol). I am getting the row/column headers but not the figures or the numbers under them.
View 2 Replies
View Related
Jun 23, 2013
I am trying to use queries I have been running in MS SQL Server Management Studio, to return data in Excel where it would display as pivot. Some queries I was able to use through Excel but few others are not returning anything.
I am doubting it has something to do with the query itself - but they are displaying results properly in MS Query, they are just not returning any data to Excel.
View 3 Replies
View Related
Sep 20, 2013
I am trying to set up a spreadsheet in Excel 2003 that pulls data from a lot of different websites and formats it all for me. I have managed to do this with a few websites but there are two which I'm having problems with.
This is the table I am trying to get, but when I do a manual web query and select the table and click OK, it says that no data has been found.
[URL] ...
View 1 Replies
View Related
Nov 23, 2011
I am trying to query our corporate directory to copy it into excel. The problem is that the table i want to copy has 90 pages. I do not know VBA very well at all. Is there code that I could input that would copy the table on page 1, paste to excel, then move to page 2 and copy/paste to the next empty row?
View 1 Replies
View Related
Apr 11, 2012
I have recently installed excel 2007 how do you edit and ODBC query from the spreadsheet. In 2003 you right clicked on a cell that opened a dropdown where you selected edit query.
View 1 Replies
View Related
Nov 7, 2012
I am using Excel 2007. I am having problem with web query. I want to get data from Komatsu parts catalogue, komatsu parts book, komatsu parts search, komatsu parts numbers, komatsu parts database this website. In excel 2007-> get external data tab->from web-> and i typed the website(Komatsu parts catalogue, komatsu parts book, komatsu parts search, komatsu parts numbers, komatsu parts database) and click go then i receieved Script error
[An error has occured in the script on this page]
Line: 3
Char: 1373
Error: Expected identifier, string or number
Code: 0
[URL] .....
Do you want to continue running script on this page?
yes no]
like that i received an error
View 1 Replies
View Related
