Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Remove All Connections?


I've got a little program which uses webqueries to import data from anywhere between10 and 200 web pages. It's working pretty well, but I need a way to clear out the residual connections via the code.

The part of the macro which imports the data was simply recorded and very slightly modified. I've included it below... but what I'd like to do once the data has been imported is delete the connection.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
DB Connection Lockout Other DB Connections
I have a series of external database connections that I run in Excel, connecting to a Server based DB and running some SELECT statements. (ingres). We may have up to dozen of client machines accessing the ingres DB at any time. All is good at this point but when excel goes to INSERT some calculated data back to the main database, it appears that my Excel spreadsheet connection is locking the database when it connects, keeping other people from using their client connections.

first question: Does anyone know if the connection excel uses to run sql statements via external database connection will indeed prevent other users from connecting to the main database?

second question: if so, is there a setting or something that can make excel more "passive"?


View Replies!   View Related
Access Databse Connections In VBA
From within VBA I need to open a database connection and issue a select and store the results in my spreadsheet. The issue is that I only JUST learned that you can do this and I dont know where I can look for instructions. Incase it matters, I am trying to connect to a Sybase DB. I have used perl, c# and a little bit of Java to do this but never from with in Basic (let alone basic within excel!)

View Replies!   View Related
Editing Data Connections In 2003
I have a workbook with multiple worksheets and over 25 Data Connections using MS Query to connect to different tables in an SQL Server database.

The problem is that the SQL Server database is now being moved to a different server. The database name and the schema of the tables will be unchanged. Only the server name needs to be changed from XXXXX to YYYYY.

I have been unable to find a way to successfully edit the data connections in Excel 2003. A web search produced one potential option. However, that did not work for me very well. Thought the code is designed to cycle through all tablequeries and pivotqueries, the change is only made to one of the data connections. Here is the option I tried:

http://support.microsoft.com/kb/816562

Note that it is very easy to edit data connections in Excel 2007. So I also tried opening the .xls file in Excel 2007. I used the Data Connections button under the Data tab in the ribbon and was successful in editing the connections to point to the new server. However, the graphs used to display the queried data behave weird. The "format axis" settings and chart sizes change. In general, default display settings for charts seems to differ between Excel 2003 and 2007.

I used Excel 2007 to only edit the data connections and saved the workbook in the original .xls format

View Replies!   View Related
Defining Column Reference With ADO Connections Using VBA
It extracts Employee ID's and there Names from our database and lists them in columns A:C

What I want to do is list the ID's manually and get it to extract only the Names for the ID's I listed in Column A...

I am not sure how to amend this code to look at ID's listed in column A instead of extracting the whole database.

I know I need to add a "WHERE ID =" clause in the SQL but not sure how to say WHERE ID = A1, A2, etc.


View Replies!   View Related
SQL Connections (take The Value Of A Certain Cell (client Number) And Set Another Cell To Be The Name Of The Client)
I'm trying to do here is take the value of a certain cell (client number) and set another cell to be the name of the client. For example if I were client number 100 and my company name was Holman Inc the user should be able to enter 100 and Holman Inc would populate itself in the next field.

View Replies!   View Related
Remove Non-alpha Characters From Alphanumerics With Option To Remove Numbers
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).

Option Explicit

Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function

Two requests:

1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?

2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"

View Replies!   View Related
Remove Data
I Want to Delete Data.

Filter I Column And Delted "N" ....

View Replies!   View Related
Remove The Word
how to automatically remove the word " Total" that Excel puts beside what you are totalling while using a subtotal?

For example, I am totaling a value of orders for one company and Excel puts "COMPANY NAME Total". Or is there a way to use the Right function to delete the 5 last letters of text in a cell? I've tried using the right function but can't seem to get it to work to delete text...

View Replies!   View Related
Remove Certain Duplicates ....
Is it possible to scan through column A for duplicates if found delete the row that has not got any data in either column D E or F? If Duplicates are found and neither have any data in D E or F Delete all but one of the duplicates.

View Replies!   View Related
Remove Duplicates
I have a sheet that looks like this.
JOE D.02082008/28/199 H PAnnual Increase06/05/2006
JOE D.02082008/28/1995 H PReclassification06/11/2007
JOE D.02082008/28/1995 H PReclassification02/11/2008

The name is in Column F the employee Number is the 020820 and we want to only have the latest date in the last column.

So out of this data above. The bottom one with the 2/11/2008 is the one we want to keep.
And delete those other 2.

The next group has duplicate names 10 of them. But only want to keep the most recent.


View Replies!   View Related
How To Remove A Macro?
I done running a macro but I want to convert back before the macro is run. How can I do it?

View Replies!   View Related
Remove A Digit
I have column a with 1000 ten digit numbers. I need to remove the
digit in the fourth place from the left side in the entire column. I don't know how .


View Replies!   View Related
Remove The Sheet
I am looking to remove the daily expenses summary sheet and continue to use the expenses detail sheet, but on the main weekly business sheet where you enter the financials, I need the expenses line to contain expenses for each day.

It is my intention to simply enter monday in the day colum in the expenses detail sheet and in the weekly business sheet it would pick this up.

So the formula I had was =ifsum(!expenses detail a2:a40, !expenses detail monday, e2:e4) This formula is giving me a name error.

Am I using the wrong formula. Basically I want this type of formula so under the expenses row for mon-sun it will add this up.


View Replies!   View Related
Remove The Spaces
I am trying to remove spaces in front of a number (currently formatted as text)

I have tried "Text to Columns", "Trim", and other suggestions in previously threads.

I have copied bank statement amounts from an e-mail and the $ amounts have one Space in front of them. When I use the Trim function, and then a paste special, I still cannot get rid of the space, and so cannot add up the amounts in this column.

Ex:

*7 Dec 2007*DEPOSIT*3,917.63
*7 Dec 2007*DEPOSIT*1,890.58


View Replies!   View Related
Remove The First 4 Digits
1. Remove the first 4 digits from each "Appeal ID"

2. Insert a new column (first column) called "Chapter"

3. Run a v-lookup down the new column against a file that is stored on my desktop. The v-lookup will cross check the Appeal ID against the file to identify the Chapter

4. Sort the data alphabetically by Chapter

5. Create seperate Excel files for each Chapter ...

View Replies!   View Related
Remove The Percentages
I have data that I import from another source into excel and it pulls over like this

3866.00%
435.00%
125.00%
1254.00%

I want to remove the % since these aren't actually percentages, the report we pull from has them listed incorrectly and cannot be changed since it is software driven, not excel driven. The problem is simply remove the % changes the number to 38.66 instead of leaving it at 3866.

View Replies!   View Related
Remove All The Dashes
Have a column containing data which may have 1-3 dashes locates within
each cell value.
Such as:
125-457A
158-857-1116
124184A-1
125-8745-22D9
458-2145-002-7

Would like to remove all the dashes "unless" the last dash to the right
is followed by a just a single digit. The results would be as follows:

125457A
1588571116
124184A-1
125874522D9
4582145002-7

On the example data, it kept 2 of the dashes in the cell values,
because the were only one digit away from the extreme right, but
removed all others.

View Replies!   View Related
Remove The Toolbars
I have a slight problem with some code I am using. I have a user form where I force the users to enable Macros using the hidden sheets trick. Once the user enables the macros all of the toolbars dissapear from excel. The code I use to do this is below:

View Replies!   View Related
Remove N/a From The Cell
who to make cell cell display a balnk/nothing.

Im using the following function

=VLOOKUP(C15,Estimator!C8:D17,2,FALSE)

View Replies!   View Related
Remove Diacritics
I need to conver a string that conatins diacritics "Stanišiæ" (note the s and the c) into a plain text version, = "Stanisic"

View Replies!   View Related
Remove A Password
I have givien a password to a workbook through the save as meathod. How do I remove this password so anybody can just select the workbook to open it?

View Replies!   View Related
How To Remove All Shapes
i have an excel file for using my manufacture planning. i dont know why but there was created too many TextBoxes(but its shapes)

I'm giving my excel file , you can see what i'm talking about at G column and 193. field
if you can solve this problem, i will be pleasure to you

View Replies!   View Related
Remove Everything To The Left Of...
I need a formula that can remove all characters to the right of "]" for example, if a1 = abcdefg]1234, I want b1 to return the value: 1234. I tried =RIGHT(a1,FIND("]",a1)-1), but that didn't really work. It returned: fg]1234

View Replies!   View Related
Remove Values
Sheet1  ABCDEFG1DocS.DE.DDESCFCPOSOLD2202204/27/200904/26/2010  DG121213202204/27/200904/26/2010Laptop123DG121214202204/27/200904/26/2010Laptop123DG121215202204/27/200904/26/2010Laptop123DG121216202204/27/200904/26/2010Laptop123DG121217202305/04/200905/04/2009  DG121218202305/04/200905/04/2009Monitor186DG121219202305/04/200905/04/2009Monitor186DG1212110202404/27/200904/26/2010  AG12311202404/27/200904/26/2010Keyboard135AG12312202404/27/200904/26/2010Keyboard135AG12313202404/27/200904/26/2010Keyboard135AG12314202404/27/200904/26/2010Keyboard135AG123 Excel tables to the web >> Excel Jeanie HTML 4

ANSWER sheet 2

Sheet2  ABCDEFG1DocS.DE.DDESCFCPOSOLD2202204/27/200904/26/2010Laptop123DG121213202305/04/200905/04/2009Monitor186DG121214202404/27/200904/26/2010Keyboard135AG123 Excel tables to the web >> Excel Jeanie HTML 4

I am looking for VBA CODE....

View Replies!   View Related
How To Remove = Sign
looking for a formula to remove an "= sign" at the very beginning. hence if "=Jordan" then want to change it to "Jordan" in cell T3 only.

but wait, the resulting cell T3 "=Jordan" already has a formula inside it =(U3&V3&W3&X3&Y3)

How to enter formula for removing "= sign" and not disturbing the existing formula ....

View Replies!   View Related
Remove The Spaces...
I have a cel that has about 100,000 phone numbers and at the end of some and before sum, there are spaces added to end. is there a formula, or a way to take everything away except for the 10 digit number?

View Replies!   View Related
Remove The Apostrophe
An apostrophe has appeared at the beginning of the words and/or numbers in the cells. I have tried using the "Replace" feature to remove these apostrophes and it won't work. I need to remove the apostrophes so I can upload our inventory, as the SKU must be pulled exactly.

View Replies!   View Related
About To Remove #VALUE! Error
Name
M1
M2
M3
M4
Total
%
A
50
45
13
-
108
#VALUE!
B
40
78
14
35
167
41.75
C
68
-
15
-
83
#VALUE!
D
97
74
16
12
199
49.75

Total
255
#VALUE!
58
#VALUE!

Q.1 :

I Want to remove "#VALUE!" error but I do not want to remove "-" sign. I want "-" sign to be treated as "0".

I also cannot use "sum" formula due to some reason & the value can be changed.

View Replies!   View Related
Remove Value From Listbox
On the form, I have a button to add files to a listbox (the box is bound to a worksheet). how to program a button to remove a line from the listbox and the worksheet? here is the code

View Replies!   View Related
Remove Zeros
Can anyone suggest a formula that will take a number (a variable amount of placeholders), and remove any trailing zeros.

eg. 6000 = 6, , 23 = 23, 230 = 23, 2300 = 23, 23000 = 23

Also looking for a similar forumla that will strip off any numeric characters from a text string of variable length.

eg ACA2343 = ACA, GNVC23 = GNVC, DAL12 = DAL, CGHJ = CGHJ, CGHJ5002 = CGHJ

View Replies!   View Related
Remove 0000001 To 1
how to remove 000000001 to just 1 from using VBA? i can do manually in excel.

View Replies!   View Related
Remove #n/a From My Cell
i have the following formula: =REPT(Estimator!D8,(Estimator!E8="Yes")). when it is not in use or false it displays #N/A.

View Replies!   View Related
Remove Add-in
I am trying to find the solution for the problem I faced after downloading the "Color Filter" utility from this forum (Handle Duplicates & Color Filter)

I really do not understand why I was banned. I've downloaded utility from THIS forum and have kindly asked help on how to remove it because it does not meet my needs. I am not an expert in VBA and expected that somebody could help me. (It looks like the person providing the utility in the above indicated post shared the password as everybody is thanking him but I don't see where it is!)

I just want to remove the utility from my PC!!! And, if I can do this without bloody password than I do not want it! But, can somebody just explain me if there is another way to remove this from my PC or I have to reinstall EXCEL ?

I must say that I find it really increadible for beeing banned twice for no reason. Isn't it worse to place the password protected utility so that the one dowloading it cannot remove it anymore?

View Replies!   View Related
Remove Characters
May I know how to remove character like

1) full stop
2) spacing
3) Dash
4) Hyphen
5) Left and Right Slash

For example:-

016-2733(LS-800E)
MS12-FS4/2M
1/4"GTR

Output:
0162733LS800E
MS12FS42m
14GTR

View Replies!   View Related
Remove Certain Characters From A Row
let's say row 2 has data that looks like

apple (kg), apple (g), orange (kg), orange (g)

it is possible to remove the (kg) and (g) tags so that it'll become
apple, apple, orange, orange

using VB code?

View Replies!   View Related
Remove Characters From Right Of String
I'm trying to remove everything after a specific character in a string.

I.e. change a website address to the hostname

http://www.excelforum.com/newthread.php
http://usa.excelforum.com/forum/new

to

excelforum.com
usa.excelforum.com

I'm using this formula, which strips the http:// and the www., but does not replace the characters after the first remaining "/" as the wildcard is not recognized.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""), A2,"http://",""), A2, "/*", "")

View Replies!   View Related
Sort/Remove Script?
Hey guys, I'm from a Laptop Repair Company. Sorry to barge in here and ask for help randomly, but any suggestions would be greatly greatly appreciated. I post mostly on Actionscript forums to help others using AS3.0, so if you have a question there I could definitely help you lol

I have 2 .xls documents with customer contact information. One has a list of old contacts I need to remove which is about 3k this list contains only email addresses. The other contains the full list of Customers this list is about 11k this contains everything from first/last name to billing and email addresses.

My goal is to double check to see which addresses are just typos and which are non-working.

I will need to go through the list line by line but better to only go through 3000 than 11000. So, I need to somehow remove all of the good addresses from the full list using the bad address list which is only 3k long.

Any suggestions on where to start?

View Replies!   View Related
Remove First Word In A String
How can I remove everything to the left of the first space in a String? For exapmle - 'Mr Adam Bill' should become 'Adam Bill'


View Replies!   View Related
Remove Text Header
I have two txt file where the report is being generated but importing it in excel and using text to column distorts the data plus it has a header which repeats on every page... how can delete those header and tidy the sheet up. I have attached the txt header example.. Any help on this.

View Replies!   View Related
Remove Trailing Commas
Removing Trailing Commas

I have a list (general ledger) GL codes with trailing commas

Example:
123456789,123456722,123789456,,,,,

When I try to use “replace” and replace the multiple commas with nothing it turns my gl codes into a scientific number (1.23456789123456E+26), but my cell is formatted to text.

The other problem is for numbers with a single trailing comma, it would also remove the commas between the GL codes

Is there a function that removes trailing commas, or commas that are not followed by numbers?


View Replies!   View Related
Remove Duplicate Addresses
I have 2 issues i am trying to work through, i have a spreadsheet with 3 data sheet tabs, the first issue i have is in Data sheet 1 I have a lot of duplicate addresses, I was wondering if there is a way to filter out the duplicate addresses so that only one of each address is showing.

Issue 2 that i have is a lot more complicated, In data sheet 1 i have a list of medical providers that reimburst at 110% or more, each address on that list needs to get 1 letter mailed to them, but i have to keep track of how many are going to each region, i.e. Columbus, OH region, Cincinnati, OH Region, Toledo, OH Region, and Cleveland, OH region. So what i want to do is create a 3rd data sheet that keeps track of total letters sent by region, but was wondering if it was possible for this info to be automatically transfered from data sheet 1 to data sheet 3. I don't need all the information in data sheet 1 to tranfer to data sheet 3. All i need is for data sheet 3 to read off the City field in data sheet 1, and calculate 1 letter sent to that region.

I dont even know if this is possible, but it is way beyond my realm of excel knowledge which isn't much.

Any help would be appreciated. I am trying to attach the spreadsheet, however it isn't allowing me to attach it, i keep getting a database error, when i upload it. It is under the max size limit too. Not sure what's going on.

View Replies!   View Related
Remove Autoshape Line
I have a shared document I created. In has been in use for 4 months with no problems. This last month, 3 other individuals not very cognizant with Excel were using the document. One of these individuals placed a 'line' in the document. It appears that they used Autoshapes to draw the line.

I want to remove the line they have drawn. It sounds like a simple problem, but I've tried:

* Edit, Clear, All
* Edit, Cut
*Right Click the 'line', Format AutoShape, under Colors and Lines, change Line Color to No Line
* Delete a series of rows with the line in the middle row,

Everything I’ve tried to eliminate this line is not working and this is very frustrating. This type of 'line formatting' is rampant in the department and has appeared in multiple documents, which I've had to recreate.

I'm not sure what they did, so I'm not sure how to correct this issue. I'm presuming they used AutoShapes. There are two small white circles - above and below the line. And a small green circle above the top white circle that has circular arrows surround it....perhaps a rotate feature.

View Replies!   View Related
Anyway To Remove Trailing Zeros?
Now I have a decimal column and I would like to remove all of the trailing zeros. It is using the number format. How do I accomplish this?

View Replies!   View Related
VLOOKUP To Remove Words
I'm if I can create a table of words that I want removed from a column.

For example:

Red Fast Cars
Red Slow Cars
Blue Fast Cars
Blue Slow Cars

Can I setup a VLOOKUP function to remove 'Red' and 'Blue'

I know I can create multiple work sheets and use Control + H, to remove words. But that entails a lot of cutting, pasting, and filtering. Plus this example is simple, I'll be using longer text strings AND some examples might need to have 3 words removed from the same string


View Replies!   View Related
Remove Trailing Spaces
I have written a function which works in the same way as the concatenate formula but where required it uses an underscore to make up the length (14 Characters) of the result. I seem however to have hit a minor snag when users input trailing spaces. I thought I could use trim to eliminate them but it doesnt seem to be working

Function HypCon(CorpAcct, Subdiv)

Dim n, i, iLen, iLen2 As Integer
Dim sCorpAcct, sSubdiv As String
Dim iLen3, iLen4 As Integer
iLen = Len(Trim(CorpAcct))
iLen2 = 7
iLen3 = Len(Trim(Subdiv))
iLen4 = 5..............


View Replies!   View Related
Remove Item From Array
Is there a vb code to remove single item from an array by specifying the index. for example, MyArray ("A", "B", "C", "D"). If I want to remove "C" from the array, is it possible to somehow remove it by refering to it by its index (2). Does 'RemoveItem' command only work for a ListBox?

View Replies!   View Related
How Can I Remove Symbols From Column
I have a column with e mail addresses that all have > < this symbols in the beginning and at the end, how can I remove them from the column?

example
<Ana.Gomez@taca.com>

View Replies!   View Related
Special Character Remove
I have sentences which contains special characters (mentioned below) in a single column. I need to remove all special characters other than space. Could anyone help me on this...

!@#$%^&*()-_=+{}[]|~`?/,:;"

View Replies!   View Related
Remove Html Markup
How do I remove HTML markup from and cell.

i.e.
<DIV class=productdesc> <H3>SCALLYWAGS CHANGING MAT. </H3><TABLE border=0>
<TBODY> <TR> <TD> <UL> <LI>Foam-filled <LI>Wipe-clean surface <LI>Fits most
dressers <LI>dimensions: 75 x 46cm </LI></UL></TD></TR>
<TR></TR></TBODY></TABLE> <UL> <LI>When using a changing mat on a dresser or
other raised surface, never leave your baby unattended even for a moment
</LI></UL></P></DIV>

I want it to read:-

SCALLYWAGS CHANGING MAT. Foam-filled Wipe-clean surface Fits most dressers
dimensions: 75 x 46cm When using a changing mat on a dresser or other
raised surface, never leave your baby unattended even for a moment

I am trying to change out website csv file into a froogle csv file but
without the HTML

View Replies!   View Related
Remove Lines From Comments
Here is a picture example of what I mean, just randomly found on google:
http://www.projectwoman.com/uploaded...ent-763577.jpg

Maybe an insignificant thing I want, but can the line from the comment connecting to the red indicator be removed? Added an example since my comments show up below the selected cell with VBA code.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved