With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.
This is a two part process:
Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")
Here is part one
Code:
Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
, Destination:=Range("$A$1")).QueryTable
[code]...
Part one works perfectly.
Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.
I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.
As follows:
In particular: *E3 from Part 1*, etc.
Code:
"SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
, _
"tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
)
Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.
1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.
2. Create a nested if table and
Change
Code:
WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"
to the value of that nested if table
The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.
3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.
What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.
My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?
The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.
Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).
I'm trying to create a dropdown list on my "job master" sheet with reference to cells in an ODBC connection where a position "description" will give me a list of names. Then I want to be able to select another list of names individually with reference to the name I Just selected.
Staff Description Foreman
[Code].....
*I was able to do this using lists and indirect list (Personnel Sheet) but it doesn't work with my ODBC connection. I attached the excel file I am using.
I have a sheet that contains a Microsoft Query (SQL) data connection. In cell C3, I have the month name and I also have a year cell (C4), which contains just the year based on what the current month (=YEAR(TODAY())) is.
For this example let's use May (C3) and 2013 (C4)
C3 and C4 are used as parameters for the SQL query, so if I change the month to January and the Year to 2010, data is refreshed to bring in that data.
Works smashingly.
I then want to add 11 more sheets (12 total), so each sheet is a month. The value in cell C3 on each sheet will be the month name.
If I just copy this sheet 11 times, Excel adds 11 extra data connections; 1 for each sheet. As the query in each sheet needs to be exactly the same, I don't want this. I want the 12 sheets to use the same data connection, only with the specified parameters at the sheet level.
Otherwise, if I need to add or remove a column, or change a column name, rather than just doing it once, I'd have to do it 12 times.
This can't be right, surely, as that would be the most ridiculous design flaw I've ever seen in a piece of software. How can having the same query 12 times be a good thing?
I'm a bit concerned that googling 'excel data connections multiple sheets' always wants to be 'excel multiple data connections one sheet' - I want 'excel multiple sheets one data connection'
I am currently querying data from an Oracle database through MS Access and then passing it to Excel for the user to work. Not all users have MS Access so I would like to automate the data pull from Excel
i currently have an excel based application with VBA code. I'm trying to add some controls around the app by prompting user for login and password credentials prior to ODBC connection at runtime. So when application is launched, prompt appears, user enters login and password, if correct, it goes on to verify user has ODBC connection at runtime.
I have been working on some ODBC queries in Excel 2010.
First, I recorded them using Excel's Record Macro function, to get an idea of what sort of source data I would need. Then, I rewrote them into something a little more intelligible.
Pulling out all the sensitive info, I want to know how to add the .ListObject.DisplayName property back onto the results of the query. I tried doing it intuitively, but it didn't work. It was in there when I recorded the macro, but I can't seem to figure out where to put it back into the re-written code. Other parts of the code depend on the results of the query being a 'named field'.
[Code] .....
Also, where would I find out what all that stuff in Cnnect means? DBA, APA, EXC, FEN, etc are all just assignments, and I might like to change some to make this run a bit quicker, if I knew what they meant.
With Excel 2010 a yellow warning bar is shown when opening a file with data connection. I don't want to show this warning to other users who will be opening the file, especially when they won't be able to update anything since the original data is on my computer. The logical way would be to remove or disable the connections before sending the file out. But when removing the data connections through Connection window all the pivot tables are converted to static tables, which is not acceptable. Is there any other solution to this? Maybe use a macro?
PS: I know that these warnings can be turned off though Trust Center, but I am not interested in changing these settings for other users.
Im using Excel 2010 and have a data connection that I want to use. I added it to a regular workbook and it updates perfectly as-designed. However, I need this feature to work in a shared workbook, but it doesn't.
I've been asked to produce a spreadsheet that monitors the time it takes to answer queries from partner companies. Ive attached some of the spread sheet that im struggling to work out. I would like a formula that will total the number of days it takes us to answer the queries per month for each company.
Example - For January Compnay A 8 Days Company B 7 Days Company C 13 Days
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.
I have no problem setting ODBC connections in vba, that return the results to a selected Range destination, but short of then setting txtVariable = Range("A1") is there any way to directly tell the ODBC connection to set the result of the query to txtVariable?
I would like a cell to show either OK or No depending on the requirements of three other cells as below
G4 is the cell to show a value. For OK a date in G6 must be more than todays date, a tick must be in F6 and a date in D6 must also be more than todays date.
I can get G4 to do each section individually i.e. (A4 has todays date in it, updating automaticaly)
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
VB:
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String StringReturn = Str1 & Str2 & Str3 End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
I am trying to create a macro that will copy data from one sheet to four other sheets depending on some requirments in the data. I have a main worksheet that will store the data, the name on this sheet will change daily, so you cannot use a "named" sheet in your macro. The other three names of the sheets are "Esky", "Indy", "Gfld", and "Other". When copying the data, I need to copy the entire row of data to the required sheet. The data comes in such as:
Based on the FIRST TWO characters in the first column, I need to copy the data to the specific sheet. I would assume a simple LEFT() function would work for this. Here are the requirments for each sheet:
Esky - "MZ" or "51" Indy - "IZ" or "Z" or "21" Gfld - "PZ" or "22" Other - Anything else
I need to return a value dependent on a few criteria. Type will be selected via a drop down and then it must lookup the same type in the top row and then the value which will be between two values and then return the grade on the far left.
I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).
Sub average()
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row For I = 3 To FinalRow If Cells(I,3).Value 0 Then 'I use the above to identify the first nonzero cell ActiveCell.FormulaR1C1 = "average(........)
This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.
I've found several posts about returning variable substrings that dealt with one or two spaces, but I have not been able to find anything that is for multiple spaces. The number of characters before the substring will remain constant.
For example:
Income from transmission agreement - 83 subs @ $0.44
In this case I need to extract: 83
Income from transmission agreement - 10,312 subs @ $0.50
Excel 2010; I am setting row heights on the basis of some parameters. I have the following statement;
If targetCell.Value = "Photo Comment: " Then targetCell.RowHeight = 185
But what I really want is to recognize only the phrase;
"Photo Comment:" in a text string that might be much longer e.g,
"Photo Comment: The photo above depicts yata, yata.........."
So, I want to recognize the first 14 character as "Photo Comment:" and then have the row height adjust to 185. So it involves "Left" and "Len" (I'm thinkin') but the syntax is beyond me.
I have an Excel sheet with lots of shortcuts in the first column. It's the entire shortcut, including the .lnk extension.
What I need to do is to be able to parse out the string to make the shortcut human readable when I create the hyperlink. I'm saving the output as HTML for a user to open these shortcuts. It's on an internal LAN & secure. Using various versions of Excel (2010, 2007 & 2003, predominately the first). Trying to make an ugly, 140+ character shortcut into a more readable form for web navigation purposes.
Up to a point, the shortcuts are similar: a123meworkfolder1folder2folder3shortcut1.lnk a123meworkfolder1folder4folder5shortcut1.lnk a123meworkfolder1folder6folder7shortcut1.lnk a123meworkfolder1folder8folder9shortcut1.lnk
The strings are the same up to the 7th occurrence of the "" character, but after that, the foldernames start to change & the shortcut may be down a level (an extra folder) or more. Also, the lengths of the strings change as well, so I can only do positional parsing up to a point.
I'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.
I need to remove numbers from a string of text and put them into new cells in Excel Starter 2010. There are two different values which need moving. The first is in parentheses and the second follows the parentheses.
Here's a few examples of what I mean:
Original text RED008 - Wickaman and Hoodlum bandwidth (0.06Gb @ 2.00) 0.12 MFR005EP - Various Artists bandwidth (0.19Gb @ 2.00) 0.38 RAZORS010 Future Cut bandwidth (0.01Gb @ 2.00) 0.01
would like to split into...
Cell 1 RED008 - Wickaman and Hoodlum bandwidth MFR005EP - Various Artists bandwidth RAZORS010 Future Cut bandwidth
Why my code is not working. I am working with Excel 2010. It will only delete the column on the active spreadsheet and not go to next worksheet. Not all worksheets contain the word "Broker".
[Code] ......
Not sure if its something to do with Activecell, try After:=ws.cells(1,1) or passing it in as the active cell stays the same i think.
I have a range of cells each containing different values. Each of these different cells has a related date contained in the row underneath.
I want to be able to search the range of cells and find it in the contents of a cell and return the corresponding date from the row below.
Excel 2010 A B C D E F G
[Code]...
Row A4:O4 is the range of cells to be tested. Row A5:O5 are the corresponding dates. Cell B1 is the value I want to search for and B2 is where the date I would like to appear.
In the example shown I'd like 01-Sep to appear in B2.
I've defined a number of variables in my spreadsheet. When I want to use one I type "=variablename" and 'm presented with a list menu.jpg
In the above example, I have several variables starting with the word "Harvard" (a town, not the university :-)
How do I select one of these without having to double click with the mouse.
Up and down arrows allow me to highlight an item but I cannot find a keystroke that allows me to select the highlighted item. Instead I have to lift my hand off the keyboard and use the mouse.
I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.
I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue
The code below works with the exception of the last section (trying to achieve the questions stated above).
I am using Excel 2010.
Code: Private Sub CommandButton1_Click() If TextBox1.Value = blank Then 'Need name for processing MsgBox ("Name must not be blank.") Exit Sub End If If Len(TextBox1.Value) > 12 Then
I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.