My set up: Have 50,000 rows of data. ( My row count will increase in the future. So might as well say I have a full worksheet of 64000+ rows.) All Data is TEXT, no formulas, etc. Column A is open Columns B thru AC contain the Data that needs to be concatenated The Data in the rows once concatenated to Column A will contain 60,000 di...
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 ...
I'm using COM in LotusScript (Lotus Notes) to make Excel print several sheets in one workbook to PDFCreator, then make PDFCreator combine then into one PDF. The problem is that calling Excel's PrintOut method immediately followed by PDFCreator's cCombineAll method results in one or more sheets being omitted from the PDF. It seems like Ex...
I'm in the midst of upgrading a MS Dynamics SL (6.5sp1) installation, and was wondering if there was an easy way to review the BSL/VBA code involved in screen customizations. Or at least a way to be able to discover each and every form that has customizations. ...
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...
Hello We are using the XML or Word ML format from Office 2003. How do I get a macro to run after a MS Word ML document has been merged by another application (not Word) called SIMS by Capita. How can I get the Macro to run after a merge, when the document is being previewed? ...
I have some "clever" users who have written an excel spreadsheet that automates some of their tasks on our intranet/timesheet. It seems as if the instance of Internet Explorer that is created is not doing everything I expect a browser to do, e.g. set cookies. Is there a way to detect if a user is hitting a web site with a VBA com object ...
There has got to be a simple(r) way to do this. I have a situation where I am listing parts using continuous forms. The parts listing has things like the part type, manufacturer, part number and the quantity. The list is of the part instances & quantity. I can look the other things up from the instance of the part, so displaying exist...
Hi. Excel is a great spreadsheet software, a even greater front UI for Business Intelligence in many companies. Often VBA is used to write the extensions, or to call other DLLs. As Excel itself is functional, F# will company Excel very well to write data analysis procedures. I am thinking whether F# or a variant of F# will become ne...
I am trying to loop through a list of names in an Excel spreadsheet using VBA and I would like to paste a surname into a text box on a webpage and then extract the returned phone number into a cell after the page has been submitted. I can only do this one name at a time so I would like to automate this process as there are several hundr...
Hello, is there any way to determine from an Excel session, which DLL an UDF is coming from ? I have a bunch of DLLs loaded by default, I wanted to determine for every UDF where it was defined. If the UDF was not defined in a DLL but via VBA, is there a way to determine in which XLA or XLS it was defined ? Thanks very much for taking...
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...
I'm using a variant on code seen in "How to make XMLDOMDocument include the XML Declaration?" (which can also be seen at MSDN. If I change the encoding to "UTF-16" one would think it would output as UTF-16... and it "does"... by looking at the output in a text editor; but checking it in a hex editor, the byte-order mark is missing (despi...
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 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...
In the 'code behind' of the report is there anything I can do in code to query a property of the record source. Sorry I'm new to access and VB, but I am wanting to achieve something along the lines of this If Me.RecordSource["MYFieldName"] = "dan" Then //do something End If Is this possible? ...
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. ...
In Excel 2003 I'm getting a Runtime error 1004: "application-defined or object-defined error" on the last line of this code (commandtext = abc) Sub SCommandTxt() Dim abc as string abc = Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText = abc End Sub T...
Hi There First of all I'm going to state right out that I've never worked with VB in the context of coding macros before - my skills lie in other areas (PHP, Javascript, getting there with C#, etc). However, I've been asked by a colleague to lock down a document so that the user cannot change font faces, sizes or colours but does still ...
I have a VBA UserForm (in a Word document), that has some CheckBox controls inside a Frame control. All of my CheckBox controls are set to use the Tahoma font (the default for controls), but one of them is showing as Verdana. That is, it says Tahoma in the properties dialog, but the font actually used to display the text is Verdana (bot...