Posts by XL-Dennis

Re: fill in website form thru vba? here it is!

I like the WinHttp as it's optimized to send request through the HTTP protocol however I wouldn't use it in the NET-enviroment as we have the System.net classes to use

When using ADO to query and retrieve data from open workbooks a memory leaks occurs and Excel's performance decrease. This is a known bug and it's described in the following KB-article at MSFT:
Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO)

But what if we want to use a "database-approach" to query open workbooks? One solution is simple to use DAO (Data Access Objects), a technology that was replaced by ADO in the mid 90's. In short, DAO was explicit targeting and optimized for the Microsoft Database Jet Engine and since MSFT wanted to have a more wider platform ADO was developed, which is also the present standard, even if ADO.NET is coming up.

What we need:
* Microsoft Windows 98/2000 and later.
* Microsoft Excel 2000 and later.
* The library DAO 3.5 and later.

As the example use early binding You need to set a reference to the Microsoft DAO 3.5 Object Library.

Option Explicit


Sub DAO_Database_Approach()
Const stExtens As String = "Excel 8.0;HDR=Yes;"
Const stSQL As String = "SELECT * FROM [Sheet2$] WHERE Dept='cc';"


'Variables for DAO.
Dim DAO_ws As DAO.Workspace
Dim DAO_db As DAO.Database
Dim DAO_rs As DAO.Recordset
Dim strDb As String


'Variables for Excel.
Dim wbBook As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rnTarget As Range


Set wbBook = ActiveWorkbook
Set wsTarget = wbBook.Worksheets(1)


With wsTarget
    Set rnTarget = .Range("A2")
End With


strDb = wbBook.FullName


'Instantiate the DAO objects.
Set DAO_ws = DBEngine.Workspaces(0)
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
Set DAO_rs = DAO_db.OpenRecordset(stSQL, dbOpenForwardOnly)


'Write the Recordset to the target range.
rnTarget.CopyFromRecordset DAO_rs


'Close.
DAO_rs.Close
DAO_db.Close
DAO_ws.Close


'Release objects from memory.
Set DAO_rs = Nothing
Set DAO_db = Nothing
Set DAO_ws = Nothing


End Sub

Datatypes
One common issue when working with workbooks as data sources is about the datatypes. This can be a complicated issue unless we have insight about datatypes. Here is a KB-article at MSFT that gives the basic:
Excel Values Returned as NULL Using DAO OpenRecordset

An excellent source that gives a deeper insight and workarounds is the following post: Mixed datatypes

Re: Filter with multiple criteria

Quote

How do I point the criteria to a cell in another sheet?

As the following snippet code shows:
[vba]
Dim wsNamen As Worksheet
Dim rnSource As Range

Set wsNamen = ActiveWorkbook.Worksheets("Namen")

With wsNamen
Set rnSource = .Range("F2")
End With

MsgBox rnSource.Parent.Name & "!" & rnSource.Address
[/vba]

Re: Select Case Vs If

Thanks Andy

I've made a note about it and will take a closer look, especially in view of Your results compared what Mr Bullen et all says in their book. I'm planning to increas the number of IF/SELECT.

Re: Select Case Vs If

norie,
As they textual support "my way" I've not seen any reason to make any tests on my own

However, I wouldn't per se claim it's faster if we use a limit number of statements as we nowadays usually have extreme powerfuler computers.

Andy, how many statements did You use in Your test?

Re: Select Case Vs If

Hi guys,

Personally I have always prefered to use the IF-approach then the SELECT-approach.
Therefore I was pleased to read in the book "Professional Excel Development" by Mr Bullen, Mr Bovery and Mr Green that it's also faster then the SELECT-approach

Re: ...a ms query that i to refresh upon the workbook opening using vba within Access

norie,

I total agree

Re: ...a ms query that i to refresh upon the workbook opening using vba within Access

norie,

Depending on the situation the approach may be an disadvantage, especially if we are about to open a workbook with several external QueryTables and Pivottables.

Re: Which formulas are using range names?

You're welcome but I should had noticed it already in my first post.

Re: Which formulas are using range names?

df,

I believe there was some minor error with the counter in the first example which I apologize for so here come a revised version:

[vba]
Option Explicit

Sub Document()
Dim wsSheet As Worksheet
Dim rnFormulas As Range
Dim rnName As Range
Dim stAddress As String
Dim lnItems As Long, lnCounter As Long
Dim vaNames As Variant

With ActiveSheet
If Not .ProtectContents = True Then
On Error Resume Next
Set rnFormulas = .UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rnFormulas Is Nothing Then
MsgBox "No cells with formulas was found in the " & ActiveSheet.Name, vbInformation
Exit Sub
End If
Else
MsgBox "The sheet " & ActiveSheet.Name & "is protected!", vbCritical
Exit Sub
End If
End With

Set wsSheet = ActiveWorkbook.Worksheets.Add

'Names to be looking for.
vaNames = VBA.Array("auditors", "check_dates", "clean_audits")

With rnFormulas
For lnItems = LBound(vaNames) To UBound(vaNames)
Set rnName = .Find(What:=vaNames(lnItems), LookIn:=xlFormulas)
If Not rnName Is Nothing Then
stAddress = rnName.Address
Do
lnCounter = lnCounter + 1
With wsSheet
.Cells(lnCounter + 1, 1) = rnName.Parent.Name & "!" & rnName.Address
If rnName.HasArray Then
.Cells(lnCounter + 1, 2) = "{" & rnName.Formula & "}"
Else
.Cells(lnCounter + 1, 2) = " " & rnName.Formula
End If
.Cells(lnCounter + 1, 3).Value = vaNames(lnItems)
End With
Set rnName = .FindNext(rnName)
Loop While Not rnName Is Nothing And rnName.Address <> stAddress
End If
Next lnItems
End With

wsSheet.Columns("A:C").EntireColumn.AutoFit

End Sub
[/vba]

Re: Adding diagramms in VBA

Quote

well I suppose sometimes you cant approach VBA problems with logic

Logic can be applied to VBA as long as we follow the protocol that have been set up in VBA.

Are You using "Option Explicit" on top of the module?

Re: Difference between IsNumeric and IsNumber?

Werner,

Great and I total agree with You

In Swedish these two words means also the same so the context they are used in gives the "final" meaning of them.

Re: Which formulas are using range names?

df,

When executing the code to You have the sheet You want to document as the active sheet?

1. Activate the sheet You want to document.
2. Choose the command ALT+F8 which will bring up the form "Macro".
3. Select the procedure and click on the Run-button.

Any improvements?

Re: Mouse Scroll Wheel in VBA

You're welcome

Re: Which formulas are using range names?

df,

Post Your code so we cant take part of it enabling us to investigate it further.

BTW, pls call me Dennis

Re: Difference between IsNumeric and IsNumber?

Werner,

As we all know (or should know) there exist no stupid questions as long as they are raised in a polite way.

Let see if a french person and a swedish person can make any sense at all

[vba]
Option Explicit

Sub test_Numeric()
Dim stExpression As String
Dim iNumeric As Integer

'Here is the first expression to be evaluated.
stExpression = "A1"

'Check if it can be converted to a numeric value
MsgBox IsNumeric(stExpression)

'Since the string value both has an A and 1 it can't
'converted to a numeric value, i e False.

stExpression = "1"
MsgBox IsNumeric(stExpression)

'The string value contain a value that can be converted
'to a numeric value.

'Therefore we can convert it like the following.
iNumeric = CInt(stExpression)

End Sub

Sub text_Number()
Dim stValue As String
Dim iValue As Integer

stValue = "1"
iValue = 1

'Since the datatype is String the value is not stored as a number
MsgBox Application.IsNumber(stValue)

'Since the datatype is Integer the value is stored as a number.
MsgBox Application.WorksheetFunction.IsNumber(iValue)

End Sub
[/vba]

Re: Difference between IsNumeric and IsNumber?

Hi Werner,

Good question

In short:
ISNUMERIC check if a value can be converted to a numeric value.
ISNUMBER check if a value is stored as a number.

Does it clear the mud?

Re: Which formulas are using range names?

One way is to do the following:

[vba]
Option Explicit

Sub Document()
Dim wsSheet As Worksheet
Dim rnFormulas As Range
Dim rnName As Range
Dim stAddress As String
Dim lnItems As Long, lnCounter As Long
Dim vaNames As Variant

With ActiveSheet
Set rnFormulas = .UsedRange.SpecialCells(xlCellTypeFormulas)
End With

Set wsSheet = ActiveWorkbook.Worksheets.Add

'Names to be looking for.
vaNames = VBA.Array("Test", "Another", "OzGrid")

With rnFormulas
For lnItems = LBound(vaNames) To UBound(vaNames)
Set rnName = .Find(What:=vaNames(lnItems), LookIn:=xlFormulas)
If Not rnName Is Nothing Then
stAddress = rnName.Address
lnCounter = 1
Do
lnCounter = lnCounter + 1
With wsSheet
.Cells(lnCounter, 1) = rnName.Parent.Name & "!" & rnName.Address
If rnName.HasArray Then
.Cells(lnCounter, 2) = "{" & rnName.Formula & "}"
Else
.Cells(lnCounter, 2) = " " & rnName.Formula
End If
.Cells(lnCounter, 3).Value = vaNames(lnItems)
End With
Set rnName = .FindNext(rnName)
Loop While Not rnName Is Nothing And rnName.Address <> stAddress
End If
Next lnItems
End With

wsSheet.Columns("A:C").EntireColumn.AutoFit

End Sub
[/vba]

It documents the active sheet for address, formulas and names.

Re: deleting contents from all work sheets

OK, so You want to have a template workbook.

1. Clean up the present workbook as You want.
2. Save it as a template-file, that is with the file extension XLT.
3. Make it available.

For more information about XLT see the directhelp and post back with specific questions.

Re: deleting contents from all work sheets

Chandhar,

If You want to clear *all* from the workbook why not just delete the file and create a new one with the same name?