I have an excel spreadsheet in a format similar to the following... | NAME | CLUB | STATUS | SCORE | | Fred | a | Gent | 145 | | Bert | a | Gent | 150 | | Harry | a | Gent | 195 | | Jim | a | Gent | 150 | | Clare | a | Lady | 99 | | Simon | a | Junior | 130 | | John | b | Junior | 130 ...
I have an embedded PowerPoint presentation in an Excel workbook. How can I edit this (open, copy slides, add data to slides, close) using VBA? ...
A listbox is passed, the data placed in an array, the array is sort and then the data is placed back in the listbox. The part that does work is putting the data back in the listbox. Its like the listbox is being passed by value instead of by ref. Here's the sub that does the sort and the line of code that calls the sort sub. Private S...
As part of a VBA program, I have to set the background colors of certain cells to green, yellow or red, based on their values (basically a health monitor where green is okay, yellow is borderline and red is dangerous). I know how to set the values of those cells, but how do I set the background color. ...
I have an Excel file that has a bunch of VBA and macro code in it. When I open the file in Excel I can choose not to 'enable' them - so the values in the fields all stay as they were during the last save. I need to manipulate the values as they were last saved - so I don't want the macros (which look at the current date and update valu...
Below was a macro provided, but I need to know how to do some adjustments to save it to the file name fixed to a specific worksheet, and specific cell with dates( eg. worksheet name is: AA on cell "B2" with ZZ ). So the filename will be ZZ AA 23122008.csv Thanks in advance. Public Sub SaveWorksheetsAsCsv() Dim WS As Excel.Worksheet D...
Hi I am trying to copy website data to XL 2003 I was told about using "send keys" but it doesn't work. Please could someone help me. [code] Sub CopyInternetDoc() Dim IntApp As Object Set IntApp = CreateObject("InternetExplorer.Application") With IntApp ' Change file name to suit .Visible = True .Navigate "http://tes...
I am trying to program an Excel module where it dynamically inserts code in new objects in a form that is created at design time. I am using this code where "Code" contains a string with the actual code that should go into the DstrFiles object. Dim DstrFiles As Object Set DstrFiles = ThisWorkbook.VBProject.VBComponents("DistributeFile...
I am new to Excel macros. I have some columns with headings scattered in many sheets. I would like to type a heading in some column which has the cursor and have the column with that heading copy-pasted to the column with the cursor. Is it possible to do this by recording a macro? How? If not, how do I do it programmatically? ...
I have been writing a VBA macro that opens a HTML document within Excel (in order to perform various calculations on it). Excel will search for the HTML document within the current folder. If it can't find it there it will produce a file open box where the user can browse to the location of the HTML document manually. All good so far....
I am an experienced developer notably in C#. I need to help a non-programmer friend get thigns done with Excel VBA. What is a good book for me to quickly pick Excel Macros & VBA up so that I can help my friend? We're using Excel 2007. ...
How can I automatically execute an Excel macro each time a value in a particular cell changes? Right now, my working code is: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("H5")) Is Nothing Then Macro End Sub where "H5" is the particular cell being monitored and Macro is the name of the macro....
For various reasons, I'm stuck in Access 97 and need to get only the path part of a full pathname. For example, the name c:\whatever dir\another dir\stuff.mdb should become c:\whatever dir\another dir\ This site has some suggestions on how to do it: http://www.ammara.com/access_image_faq/parse_path_filename.html But they seem rat...
I have used Excel in my VB6 apps many times before, and have never run into such a weird problem trying to accomplish something very easy.. I am trying to open an excel (xls or xlsx) file and read through values, as you can probably see. When I try to open the file, I get an error 70 (permission denied) error. The odd thing is that the...
I have a csv file of 1.2 million records of text. The alphanumeric fields are wrapped in quotation marks, the date/time or numeric fields are not. For example "Fred","Smith",01/07/1967,2,"7, The High Street","Anytown","Anycounty","LS1 7AA" What I want do is write some VBA in Excel (more or less the only tool available to me that I am ...
I seem to be getting a type mismatch error when trying to do something like this: In new workbook: A1 B1 5 4 Function Test1() As Integer Dim rg As Range Set rg = Test2() Test1 = rg.Cells(1, 1).Value End Function Function Test2() As Range Dim rg As Range Set rg = Range("A1:B1") Test2 = rg End Function Adding =...
The code snippet below changes the data validation state of a cell and runs when the Excel-2003 worksheet is unprotected. However, when I protect the work sheet the macro doesn't run and raises a run-time error Run-time error '-2147417848 (80010108)': Method 'Add' of object 'Validation' failed I have tried wrapping the code wi...
I'm trying to use an Excel Macro to reformat a spreadsheet exported using OLE-Automation The following code works OK: Application.FindFormat.NumberFormat = "#,##0.0000000" Application.ReplaceFormat.NumberFormat = "#,##0.00" Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFo...
Hi, I am a beginning in visual basic. What I am trying to do is whenever a box is clicked, highlight a specific range. Then, if another box is clicked after that, the previous range will unhighlight, and another range will highlight. Here is my code but it doesn't work right now. Dim FSelect As Boolean Dim myRange As Range Sub Rectan...
So I'm having to run someone else's excel app on my PC, and I'm getting "Can't find Project or Library" on standard functions such as date, format, hex, mid, etc. Some research indicates that if I prefix these functions with "VBA." as in "VBA.Date" then it'll work fine. Webpages suggest it has to do with my project references on my sys...