how do i use filesystemobject in VBA?
is there something that i need to reference? how do i use this: Dim fso As New FileSystemObject Dim fld As Folder Dim ts As TextStream i am getting an error because it does not recognize these objects ...
vba
How to convert a Variant array to a Range?
I have a 2D array of type Variant. The size and values that populate the array are generated based on data within a worksheet. Further processing is required on this array, the primary being the interpolation of several values. I am using this interpolation function ( I know about excel equivalent functions but a design choice was made n...
VBA Excel: Too many checkboxes on worksheet, code doesn't work
Hi everyone, I'm currently working on an MS Excel worksheet which contains 1277 checkboxes, spread over 96 rows. In this sheet, there is a column in which the total of the checked checkboxes is placed. The idea is that when a checkbox is ticked, the total sum in this control column is added by one. The code for this works fine, because...
error handling in VBA
i have a sub that looks like this: Sub open_esy(filename, p As String, p1 As Integer) Dim fileLocation As String Dim iFileNum As Integer, findblank Dim letter_temp0 As String, letter0 As String, letter1 As String Dim i As Integer Dim j As Integer i = 16 If Dir("\\Tecan3\output\" & filename & "*esy") <> "" Then fileLocation = "\\Tec...
Sanitizing a string in Access 2003 SQL: Problem with '
I'm writing a query that has to count the number of students enrolled in a course, and the way I'm doing it is like this: DCount("[student/course table].[Student ID]","[student/course table]","[StartDate] = #" & [Course Start Date] & "# AND Location = '" & tblCourseDetails.Location & "' AND [Course Number] = '" & [Course Number] & "'") ...
VBA - grabbing column names from lotus notes database using getColumnNames method
So I have a view (object name is 'view') in a Lotus Domino db from which I want to grab the column names and put them in an array: Dim view As Domino.NotesView Set view = db.GetView(viewScen) //viewScen is a string containing the actual view's name //db is a string containing the actual db name These declarations work fine, but wh...
AndAlso/OrElse in VBA
Hey I'm trying to get a lazy evaluation 'And' in my my Excel macro to do something like: If Not myObject Is Nothing *And* myObject.test() Then 'do something' Else 'do something else' End If I know this exists in VB.NET as AndAlso and OrElse but cannot find anything similar in VBA. If this actually does not exist what's the b...
Excel VBA 2007: Select all sheets except for specified ones (to perform actions onto)
I'm trying to create a macro that selects most of the sheets in the workbook and then uses ExportAsFixedFormat to print them to PDF. The problem is, while I know the sheets I don't want to include, I don't know exactly which sheets will be in place in at given time, so I can't make a static list of "Select these sheets" (It'd probably b...
How To Get The Current Year Using Vba
Hi i am trying to get the current Year using vba . Is There is any Pre- Build function is available to find out the Current Year , Thanks In advance ...
Getting the number of filled cells in a column (VBA)
Hi mates, I'm just starting with "programming" VBA on Excel to automatize some anoying manual stuff...so this will probebly sound like a total nooby question, although I'm not very familiar to VBA Objects and so on. So my quesstion: Is there a more elegant way to find out the length of a column? My first column is an index, with monoto...
Which property holds the items in the resultant Range of non-contiguous cells ?
I have used the Find_Range function from OzGrid which returns a Range object with the found items. It have used it successfully. I know that looping through each item of the resultant Range allows for updates/modifications to the individual cells. The Count property shows the right value. There is no Value property and the Value2 prope...
Connection to SQL
I want to add a connection to a sheet so that each time a cell is refreshed, a value is passed in from SQL. I want it to reference a textbox on one of the other sheets for where ID = How do I set the connection to refresh with that value. What do I put in my connection string? I can get it to work staticly. ...
Click button or execute JavaScript function with VBA
I'm trying to get my VBA routine to interact with a page that uses JavaScript, and have been successful in the past. After a recent update, it no longer works. At this point I need to either A) programmatically click a button or B) execute the function the button calls. The tricky part is that the button has no declared name. There a...
Outlook Appointment Item: Set the "Label"
In Outlook 2003, when you have a calendar event, it has an option to set its Label to Important, Business, Personal, etc, each having a different colour. This colour will be the colour of the scheduled event on your calendar. My problem is I have no idea how to change it using an AppointmentItem. I've looked through the object browser a...
Change both the code name and the shape name of an activex optionbutton control in Excel vba
Excel VBA: I am trying to get to some activex option buttons through the OLEObjects object, but I am finding that even though I change the value of (Name) in the property window for the object, it still requires the "OptionButton1" default name as a key. I know that some of the objects in Excel VBA have a code name and another name whic...
using NOT LIKE for paranthesis
here is my expression: =([Panels] not Like '*IT_AMPH,*' and [Panels] not Like '*AMPH_SN,*' and [Panels] not Like '*AMPH_S,*') can i say this instead: = not ([Panels] Like '*IT_AMPH,*' Or [Panels] Like '*AMPH_SN,*' Or [Panels] Like '*AMPH_S,*') are these expressions the same? *i need to mention that my question is not about logic i...
doCmd.openreport where clause prompting
I have a simple piece of code written to open a report. Private Sub FormButton_Enter() Dim strwhere As String strwhere = Me.FormText DoCmd.Openreport "rtpname",acViewPreview,,"ColumnName=" & strwhere End Sub I am getting two popups , even though i pass the filter criteria from the form. Please advice. ...
I receive a type-mismatch error from VBA on one computer, but not on others. Why?
Hi, I have an Excel file with macro. In this file, there is a user form. In the form, there is a Textbox. I call the user form "MyForm" and the TextBox "MyTextBox". I also have a module. In this module there is a subroutine like this Sub MySub(s As String) MyForm.MyTextBox.Value = s ... End Sub On most computers, the code ab...
VBA String concatenation
Hey Guys, Is there any Java-like solution for concatenatin strings in VBA? I want to use a MsgBox in a way like this: ... Set UT = Workbooks(dropdownValue).Worksheets(1) With UT UT_rows = .Cells(3, 15).End(xlDown).Row End With MsgBox "Rows of the file: " + UT_rows But when I do that, my code hangs up at this point. (incompati...
Excel VBA behaves different in different languages
Hello All Win-XP, Excel 2003 I have a range defined by a name, which is filled by a query. The same named range forms the source of a cell validation (in-cell dropdown list). In my VBA this range is accessed via a range object named LOVL2. Each time the range is re-filled by the query, its name is redefined to include all rows I obtai...