I want to query the table (Named range) from vba using SQL. name id Alpha 1 Beta 2 Gamma 3 I want to query like, "select Name from table1 where id =3" in VBA can someone help with the connection string that needs to be used. thanks! Srin ...
Hi, I have created a addin for excel, where there is a ribbon and a button on it. I have handled the event of button click with the following code Private Sub test_button_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles test_button.Click Dim activeWorksheet As Excel.Work...
I have an App_SheetChange() handler in an add-in that does some processing as modify their spreadsheets. I've already optimized the handler to only perform the processing if the user's update happens within the range that the processing is dependent on (only one column). But when users delete rows or insert rows, I don't need my routin...
I have been using every version of Kill routines to delete xls files in the current directory with no success. Here is my code. I want to delete every xls file except wbCntl. Stepping through this code shows that everything works with the exception of the Kill swb command. (swb is public defined as a string) 'Close all files exce...
I'm working on a project to send alert notifications to a mailing list. I've an excel file with a vba macro that permit its updating from a datatbase. I need to execute the macro in the file automaticaly every 30 minutes without having to open the file. i've been told to write this macro in a separate program, but i don't know how to do ...
Hi there, Backstory: I've been building a back-end CMS/database for a client to manage his bar's ever changing drink menu, however the CMS/database will no be ready for use for a while, and thus I also need a temporary solution to help update the website's menu. Situation: The client prints the bar's menus daily from a formated Excel...
Hi I have a function that is meant to run the ShowPages() command of a PivotTable and then save each sheet to a separate file. Here's how I wish I could do it: Sub Split() ThisWorkbook.Sheets("Data").PivotTables("Data").ShowPages PageField:="Codename" Dim newWb As Workbook For Each s In ThisWorkbook.Sheets If s.Na...
Hi all, Just have a simple question. Does CR11 R2 has CONFIDENCE function from Excel? http://support.microsoft.com/kb/828124 If not how do I get confidential value? ...
I have a spreadsheet that I use at work to keep track of returned contracts. I've set up a color key so I can quickly glance at the sheet to see which contracts still need to be returned to us and which account manager the account belongs to. The account manager's initials are listed in column A; from there I would like to color the ...
In VBA / VB.NET you can assign Excel range values to an array for faster access / manipulation. Is there a way to efficiently assign other cell properties (e.g., top, left, width, height) to an array? I.e., I'd like to do something like: Dim cellTops As Variant : cellTops = Application.ActiveSheet.UsedRange.Top The code is part of a ...
Creating a new Excel workbook as in: Dim xl As Excel.Application Dim wb As Excel.Workbook Set xl = New Excel.Application xl.Visible = False Set wb = xl.Workbooks.Add Is there an easy way to stop Excel automatically creating Sheet1, Sheet2, Sheet3? I can always delete these unwanted sheets afterwards but that feels like a clunky solut...
Hi Can we connect from excel (excel-VBA) to oracle(in a remote server) without installing oracle client in the client system... I tried all the options below but it is throwing error as oracle client should be installed. below is the connectionstring i have used 1)strConnection = "Provider=OraOLEDB.Oracle;Data Source=SourceName;User I...
In Excel VBA, I am using Excel as datasource for some queries. I am using the query as SQL = "Select * from [NameRange1]" - This works But, I want to use a table name instead of Namerange(Excel 2007 Tables). How do I use that? I tried "select * from [Table1]" and I also tried creating a Namerange for this table1 and it didn't wor...
This one has me stumped. When I set the formula for a selected cell in a ListObject, if the ListColumn is empty, Excel fills the formula for the whole column, rather than just the Selection. I have duplicated this in a separate workbook. Create a Table Insert a five or so rows Click on (select) one of the cells in the column Execute ...
Hi all, How i using current year to default in drop down excel 2003 as I know i can using Sheet30.Shapes("Drop Down 3").ControlFormat.Value = 1 in code, but how to do for current year. Thanks for all ...
I have a column with two value ranges, specifically 0-30000 and 60000+ from which I need to extract the smallest two values in the 60000 range. The only way I have found so far to to use AutoFilter to produce the subset of the required data to extract. My problem is that the Autofilter function does not return a reference to a range. If...
Hi guys, I am quite new to VBA coding so I was hoping you could help me with the following problem. I am looking for the best way to organize the following: From one set of data I am getting different sorts of documents (all have a certain document type) with their information (e.g. customer name, address, amount, VAT,...). From this...
Hi i have a java class..i have registered in my system(it has shown the message as registed successfully)..but when i try to create an object(the code is as below) It is throwing error as module not found..do i have set the path.if yes how.. Dim conn set conn = createObject("Main") Main is the java class which is in C:\windows\java\t...
Hi guys, I'm still learning VBA and I can't figure out wth I'm having so many problems with a Collections object. I have a function that adds custom objects (I created a very simple class to store some data) that does the typical "read data, create object representation, stick it into Collections" sort of stuff. If I try to add a "key...
I am looking at this piece of code that I got from here because I would like to use it. The issue is, I have used 2003 to create the "test.xls" file but when I run this code it opens in 2007. Is there any way to force this to open in a specific Excel version rather than the default? GoSub, GuiOpen pweb := COM_AtlAxCreateControl(WinExi...