Excel 2007 :: VBA SQL Query Length?

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


Can Excel 2007 Solve For Arc Length

Nov 18, 2009

I need the formulas to solve for the ARC length AB.

(How) can Excel solve this?

I will have to solve many variations of the 1 inch length and/or vary other measures.

Arc radius 1.5
Slope angle 31 degrees
Slope intercept to center of Arc Diameter 1
Circle B radius .75
Circle B is tangent to Slope
Solving the Segment length AB would be a bonus.

View 9 Replies View Related

Excel 2007 :: Form Command Button Label Length

Aug 2, 2013

We just upgraded to Excel 2007 from Excel 2000 and have run into a challenge relating to labeling a form command button. It appears that the length of text for a command button label has been shortened to 31 characters when setting the name using VBA. No such limitation shows up when I manually create such a button.

Following is the code that used to work to create the button and label in VBA (Excel 2000):

ActiveSheet.Buttons.Add(2.25, 13.5, 443.25, 17.25).Select
Selection.OnAction = "PatientSelectedButton"
Selection.Name = "CheckFormButton"

[Code] .....

If I change the string I want to use for the label to 32 characters, or less, this code works (Excel 2007).

ActiveSheet.Buttons.Add(2.25, 13.5, 443.25, 17.25).Select
Selection.OnAction = "PatientSelectedButton"
Selection.Name = "CheckFormButton"

[Code] ....

I have to admit that this is the last item that I thought would break!

View 4 Replies View Related

Excel 2007 :: Formula Length Requiring Binary Save?

Jan 1, 2013

I'm receiving this statement when trying to save:

"One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid the limitation, save the workbook in the Excel 2007 Binary workbook format."

The longest formula is 29 characters long. Even if I add the number of characters in every formula in the workbook, it only adds up to 1664.

View 3 Replies View Related

Excel 2007 :: Edit Query From Spreadsheet

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

Excel 2007 :: Script Error In WEB QUERY

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

Excel 2007 :: How To Use Query Tables With Multiple Web Pages

Apr 22, 2014

I'm trying to query a web site and export the data to a single sheet in Excel. I've used the record macro function in Excel (2007) and come up with the following but I want to query over 20 pages from this site and wondered if there was any way in looping this macro to do the hard work for me;

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.futwiz.com/en/players", Destination:=Range("$A$1"))
.Name = "players_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

[Code] ........

On the second page the URL changes to;

URL;FIFA 14 Ultimate Team Players - FUTWIZ

and the .Name field changes to;

.Name = "players?page=1_1"

and on the 3rd page I get;

URL;FIFA 14 Ultimate Team Players - FUTWIZ


.Name = "players?page=2"

View 3 Replies View Related

Excel 2007 :: Data Validation Query - Sum Of Two Cells Less Than A Set Number?

Jan 6, 2014

I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear

So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column

View 4 Replies View Related

Excel 2007 :: ODBC Connection Query Shows No Result

Mar 25, 2013

Why this sql query doesn't return values? My excel version is 2007.

if object_id( 'TEMPDB..#TMPDOC') is not null
drop table #TMPDOC

create table #TMPDOC (Code varchar(5) NOT NULL)

[Code] ...........

View 6 Replies View Related

Excel 2007 :: Workbook Use One Data Connection With Different SQL Query For Each Worksheet?

Oct 13, 2013

I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.

I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.

View 2 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

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

Query That Pulls Data From A 2007 Access Database

Nov 11, 2009

I 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 Related

Shorten String Length If Exceeds 31 Characters (max Sheet Name Length)

Mar 27, 2014

I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0

Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?

[Code] .....

View 2 Replies View Related

Formula Code To Find Arc Length From Chord Length

Sep 4, 2012

How to create a code formula to calculate the arc length from a given chord length?

If you know the radius of the major circle.

Say the chord is 50mm and major circle dia is 72mm (radius 36mm)

arc from chord.jpg

View 5 Replies View Related

Give Cell A Value Based On Length Of Row (variable Row Length)

Jan 9, 2010

My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.

The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.

For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.

So, in conclusion:

Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1

Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2

View 9 Replies View Related

Character Length Limit In Column In Excel?

Dec 16, 2013

Formula on how to limit character length limit in a column in excel. I would like to limit the column to 32 characters

View 2 Replies View Related

Importing Fixed Length Text Into Excel?

Apr 15, 2014

I am using the code below to import a fixed-length text file into Excel. As the macro is written, it imports starting at the first line of the text file. How do i tell it to start importing at line 1000 and above?

View 2 Replies View Related

Excel 2010 :: Match - Text Length Limitation

May 8, 2014

Excel 2007-2010. I'm using match(string, range,0) but there must be a limitation on the length of the string since I know the string is in the range but it returns #value as if it is not found. Is there a VBA solution to get around this without having to loop/cycle through the entire range?

View 4 Replies View Related

Macro To Selectively Import Fixed Length Data Into Excel

Feb 23, 2014

I am attempting to modify VBA code from [URL] .....

The section of the code I want to modify is below dealing with importing selective text. The original function returned a "False" if the line contained a keyword specified in the Array function. I have reworked it so that it shows a "True" if the line contains a keyword specified in the Array function.

My only problem is that it only does it when the keyword appears in the beginning of the line, as it uses the "Left" operator in looking at line. How do I configure the function so that it returns a "True" if the keywords appears anywhere in the line?

View 4 Replies View Related

Excel 2010 :: Splitting City / State / Zip From 1 Cell Into 3 No Equal Length

Jan 30, 2014

i have an excel spread sheet (2010) that contains city, st and zip in one cell. i need to separate these into 3 individual cells. My problem is the some of the cities are one, two and sometimes 3 words so using a delimiter of space will not separate them correctly.

View 4 Replies View Related

Consolidate In Excel VBA For Multiple Variable Length Sheets In Single Workbook

Jun 30, 2014

my macro comes up with an error on the Consolidate line that says 'Cannot add duplicate source reference'. I have a workbook with a variable number of tabs and I want to consolidate-sum (not copy and paste) the data from the various sheets onto a summary sheet within the same workbook. I'm at the point where it all works fine until it hits Consolidate.

Option Explicit
Sub ConsolidateExport()
' ConsolidateExport Macro


View 1 Replies View Related

Excel Summation Query From IF Conditions

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

Excel Column In Access Query Via VBA

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.


"Select * From Bsmart where Serial_Number = ##[EXCEL COLUMN C1:Lastrow]##

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

VBA / Query While Delete Sheet Using Excel

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
If UCase(Sheets(iTemp1).Name) "TEMPLATE" And UCase(Sheets(iTemp1).Name) "WORKING" Then
End If

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

How Do I Run A Query In Access Via A Macro In Excel.......

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

Filter Parameter In Excel For MS Query

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

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 View Related

Excel 2013 :: Delete Using Microsoft Query?

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:


View 3 Replies View Related

Excel 2003 :: Referencing Cell As (MS Query) Value?

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

Data Sorting Query - Get Excel To Calculate?

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
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.


If I manually sort the above data into descending order of national curriculum level it will look like this:


but if I try to get excel to do it, obviously excel just treat the levels as alpha numeric and sorts them like this:


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

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

Copyrights 2005-15 www.BigResource.com, All rights reserved