collapse subtotals to level 2 using VBA
does anyone know how to use VBA to collapse subtotals (once created) to level 2 so I only see the summary? ...
excel-vba
VBA Clean use of many Constants
My Excel VBA takes ~300 XLS files and grabs 8 cells to deposit into their own row. (Office11) I have several subs and functions that use location constants for the sourceData and destination locations. Grand Total I have 23 constant locations with Column numbers, cell locations. Question: Any suggestions on how to clean this up for r...
Excel Find Speed vs. VBA binary Search?
How good/fast is Excel VBA's Find vs. binary search? My platform is Office 11|2003 and I'll be searching for strings against Column A on three sheets of values. Total number of rows ~140,000 If worth it which Library & functions should I reference to do the sorting and then the binary search? Binary searching strings/text reportedly ...
redirecting input to an executable from Excel VBA
How do you redirect input to an executable from inside VBA? Specifically, why does the code below not work? ChDir theRightDirectory Set WshShell = VBA.CreateObject("WScript.Shell") WshShell.Run "runme < start.txt", 1, True Or RetVal = Shell("runme < start.txt", vbNormalFocus) runme.exe does start up all right, but the input is n...
VBA: Is there a call stack level limit?
I have a couple of colleagues looking at some bad code in Excel VBA, wondering is there a limit to the number of levels in a call stack ...
Excel 2003 (8316.8221) screen corruption
Hi Excel 2003 (11.8316.8221) SP3 on Windows XP - KB973475 When the my Excel VBA application starts it activates the first sheet in the workbook but shows portions of the second and third sheet "bleeding" into the first sheet. This is easily worked around by say minimising and maximising the workbook or causing it to repaint in some oth...
Send email from Excel
I'm opening an outlook email from excel using vba. I would like to format the body, eg. using a certain font and making a few words bold. Is this possible? Here is my vba for opening an email: Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = strReci...
Excel VBA: Update Pivot Souredata
I tried to record the code to update a pivot sourcedata which gave me this: ActiveSheet.PivotTableWizard SourceType:=xlExternal, _ SourceData:=QueryArry1, _ Connection:=Array( _ Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _ Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxB...
excel VBA horizontal alignment problems when using NumberFormat codes
Working on a personal project, When entering a fraction into Excel, I have found that if you don't have an equals sign in front of it then Excel may treat it as a date. I have found that if the cell's format is in fractions then Excel uses it just fine. So knowing this, I went into Excel's VBA and added this to my code: ActiveCell.Nu...
Analysing Excel VBA/Macro code
Is there a tool which will analyse/parse an entire Excel workbook and let us see all the macros, functions, VBA code? I've been given a large number of Excel spreadsheets to analyse, we need to know what they do and how they do it, with a view to creating documentation, and/or re-writing. In many instances the original developers are go...
Send PivotCache into Access Table
Bonjour, I am receiving monthly a Spreadsheet with a PivotTable and I need to retreive the source from it so I can populate a database. Here what I know from the File: The PivotTable Source is External, a Access database and no I cannot have access... It as more than 66000 lines and I am using Excel 2003, I have try with 2007 no Luck ...
Excel VBA - Find minimum of list of values?
For a list like: Column1 Column2 Column3 DataA 1 1234 DataA 2 4678 DataA 3 8910 DataB 2 1112 DataB 4 1314 DataB 9 1516 How do I get a list like this: Column4 Column5 Column6 DataA 1 ...
Excel VBA - how to return insertion point on control on form
I just have a user form that the operator will shift+tab between and other applications. When they shift+tab back to my user form I need to activate the insertion point where they were instead of them needing to click to get back in....? ...
count subtitled grouping in excel please...
Excel sheet is subtitled but need now to do a count of the items in each grouping so need to find subtilted rows by using a macro and count the number of items in each grouped section (column a) placing the count value in the relevant subtitled row in Column A. no idea where to start can anyone help. Running the subtilteld function aga...
How to check if Cell has Integer on it?
How to check if a specific Column has Integer on each cell, and if it contains a string, Insert a blank cell to row in question. ...
Convert numbers stored as text to numbers
this code does not seem to work well always when copying currency data from another sheet: Dim myprice As String myprice = othersheet.Range("H" & c.Row).Value ws.Range("C" & r).Value = myprice ws.Range("C" & r).Style = "Currency" sometimes cells have a warning that "this number is formatted as text" ...
Trouble creating a recordset from a SQL Server 2005 query with PIVOT
Here's the output code from my stored proc: SELECT * FROM ( select q.ccypair, q.vega, t.label from #parallel q LEFT JOIN TPRR_vega_weights t ON q.Tenor = t.Tenor ) a PIVOT ( Sum(Vega) for a.label in ([t1],[t2],[t3],[t4],[t5],[t6],[t7],[t8],[t9],[t10],[t11],[t12],[t13],[t14],[t15],[t16],[t17],[t18]) )p order b...
Writing to stdout from within a Microsoft VBA macro
I'm using Python (and the Win32 extensions) to execute macros in an Excel spreadsheet via the COM interface, as shown below: import win32com.client o = win32com.client.Dispatch("Excel.Application") o.Visible = 1 o.Workbooks.Open (r"C:\test.xls") o.Application.Run("macro1") What I'd like to do is have the Excel macro output text warnin...
How to get the excel file name / path in VBA
Say, I'm writing a VBA inside my excel file sample.xls. Now I want to get the full path of sample.xls in my VBA. How do I do it? ...
Optimize SQL SELECT for 4000 searches, into Excel by VBA
How could this be optimized for speed by batching or other techniques? Its a 20MB Access2003 Database I am searching from Excel 2003 VBA. I have my Access Table Keyed (autonumber) so I though this would provide intelligent non-linear searching like binary searches. Currently searching for 4000 values from a table of 147k records is ta...