Recent content by pvw

Hi Douglas With pure VBScript you can't perform file I/O. But you can use the FileSystemObject in VBScript to do this. Here is a good documentation with samples: FileSystemObject - Working with Files http://msdn.microsoft.com/library/en-us/script56/html/sgworkingwithfiles.asp Other related...

Hi Ekelund1 There is no way to force Excel to convert the formulas. The conversion runs only when you open a book in Excel 5.0 or later, which was saved in Excel 5.0 or later workbook format. The 'shared formulas' method is a feature which was introduced in Excel 5.0 and is an internal function...

Hi Ekelund1 Your sample macro writes 10'000 formulas into cells. A formula is saved as part of each cell. This means that each cell has its own formula information, when you save the book. Because the formulas are all similar to each other (they are repeated), Excel can handle them as shared...

Hi Here's an example for a combobox with 2 columns and 4 list items (rows): Dim astrItems(3, 1) As String '(rows/columns) astrItems(0, 0) = "R1C1" astrItems(0, 1) = "R1C2" astrItems(1, 0) = "R2C1" astrItems(1, 1) = "R2C2" astrItems(2, 0) =...

Hi Neil You can still use UBound. The number of records is stored in the second dimension, so you have to write UBound(output_data_array, 2) When you've got 256 records, 'UBound(output_data_array, 2)' returns 255 because the array is zero-based (by default). Add the statement Option Base 1...

Hi Neil CopyFromRecordset is a method of the range object. It's not made for reading records into an array. But you can use 'GetRows' instead: Dim output_data_array As Variant output_data_array = sqlRs.GetRows(sqlRs.RecordCount) With this, you'll get a two dimensional array. HTH Philipp

Hi sabascal There is a tool called 'Dialog Converter' from Baarns.com. You can download the original version for Excel 97 from my site: http://195.186.84.74/download/tools/DlgConv.exe The file DlgConv.exe includes the converter tool (xla Add-In) and a ReadMe.doc with hints and known issues...

Hi These two MS Knowledge Base articels describe the solution: HOWTO: Copy the Screen or Active Window to the Clipboard from Visual Basic http://support.microsoft.com/default.aspx?scid=kb;en-us;240653 HOWTO: Capture and Print the Screen, a Form, or Any Window...

Hi grrr223 I just checked the Excel VBA help. The remark about named arguments is correct but incomplete. My last example works because I used the argument name of the run method instead of the argument name of the CreateToolbar sub. Assuming CreateToolbar has 10 optional arguments and the 10th...

Hi There is a function LookupIPAdress. I think that this code can help you: www.scottandmichelle.net/scott/code/index2.mv?codenum=081 HTH Philipp

Hi Just pass the path as parameter: Shell "explorer " & ActiveWorkbook.Path The code above shows the explorer with the folder view. If you want to see the 'real' explorer view (with the tree), use the '/e' parameter: Shell "explorer /e, " & ActiveWorkbook.Path HTH Philipp

Hi grrr223 Instead of Call MODNAME.CreateToolbar(TOOLBAR) use Application.Run MODNAME & ".CreateToolbar", TOOLBAR In your code both arguments of CreateToolbar are optional. You can call the sub without these arguments: Application.Run MODNAME & ".CreateToolbar" If...

Hi max1565 The syntax seems to be ok. The only problem I can see is the declaration of 'LastRow' as data type Integer. If the last row is > 32'767 you'll get an overflow error. Variables for rows are usually declared as Long. Philipp

Hi gsgriffin I hope you're using Excel. I wrote a little function which does your task: Public Function SumBGColor(CellRange As Range) As String Dim rngCell As Range Dim dblSumRed As Double Dim dblSumYellow As Double Dim dblSumGreen As Double Dim dblSumOther As Double For Each...

Hi Kevin I just want to inform you, that the line Application.DisplayAlerts = True at the end of the code won't be executed, because the "ThisWorkbook.Close" closes the book, in which the running code is. Closing the book stops code execution. Philipp