Asked  1 Year ago    Answers:  5   Viewed   5 times

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.



Within Excel you need to set a reference to the VB script run-time library. The relevant file is usually located at WindowsSystem32scrrun.dll

  • To reference this file, load the Visual Basic Editor (ALT+F11)
  • Select Tools > References from the drop-down menu
  • A listbox of available references will be displayed
  • Tick the check-box next to 'Microsoft Scripting Runtime'
  • The full name and path of the scrrun.dll file will be displayed below the listbox
  • Click on the OK button.

This can also be done directly in the code if access to the VBA object model has been enabled.

Access can be enabled by ticking the check-box Trust access to the VBA project object model found at File > Options > Trust Center > Trust Center Settings > Macro Settings

To add a reference:

Sub Add_Reference()

    Application.VBE.ActiveVBProject.References.AddFromFile "C:WindowsSystem32scrrun.dll"
'Add a reference

End Sub

To remove a reference:

Sub Remove_Reference()

Dim oReference As Object

    Set oReference = Application.VBE.ActiveVBProject.References.Item("Scripting")

    Application.VBE.ActiveVBProject.References.Remove oReference
'Remove a reference

End Sub
Tuesday, June 1, 2021

There are many ways to use parameters in queries. I will try to provide examples for most of them, and where they are applicable.

First, we'll discuss the solutions unique to Access, such as forms, reports and domain aggregates. Then, we'll talk about DAO and ADO.

Using values from forms and reports as parameters

In Access, you can directly use the current value of controls on forms and reports in your SQL code. This limits the need for parameters.

You can refer to controls in the following way:

Forms!MyForm!MyTextbox for a simple control on a form

Forms!MyForm!MySubform.Form!MyTextbox for a control on a subform

Reports!MyReport!MyTextbox for a control on a report

Sample implementation:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Forms!MyForm!MyTextbox" 'Inserts a single value
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = Forms!MyForm!MyTextbox" 'Inserts from a different table

This is available for the following uses:

When using DoCmd.RunSQL, normal queries (in the GUI), form and report record sources, form and report filters, domain aggregates, DoCmd.OpenForm and DoCmd.OpenReport

This is not available for the following uses:

When executing queries using DAO or ADODB (e.g. opening recordsets, CurrentDb.Execute)

Using TempVars as parameters

TempVars in Access are globally available variables, that can be set in VBA or using macro's. They can be reused for multiple queries.

Sample implementation:

TempVars!MyTempVar = Me.MyTextbox.Value 'Note: .Value is required
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = TempVars!MyTempVar"
TempVars.Remove "MyTempVar" 'Unset TempVar when you're done using it

Availability for TempVars is identical to that of values from forms and reports: not available for ADO and DAO, available for other uses.

I recommend TempVars for using parameters when opening forms or reports over referring to control names, since if the object opening it closes, the TempVars stay available. I recommend using unique TempVar names for every form or report, to avoid weirdness when refreshing forms or reports.

Using custom functions (UDFs) as parameters

Much like TempVars, you can use a custom function and static variables to store and retrieve values.

Sample implementation:

Option Compare Database
Option Explicit

Private ThisDate As Date

Public Function GetThisDate() As Date
    If ThisDate = #12:00:00 AM# Then
        ' Set default value.
        ThisDate = Date
    End If 
    GetThisDate = ThisDate
End Function

Public Function SetThisDate(ByVal NewDate As Date) As Date
    ThisDate = NewDate
    SetThisDate = ThisDate
End Function

and then:

SetThisDate SomeDateValue ' Will store SomeDateValue in ThisDate.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeDateField] = GetThisDate()"

Also, a single function with an optional parameter may be created for both setting and getting the value of a private static variable:

Public Function ThisValue(Optional ByVal Value As Variant) As Variant
    Static CurrentValue As Variant
    ' Define default return value.
    Const DefaultValue  As Variant = Null

    If Not IsMissing(Value) Then
        ' Set value.
        CurrentValue = Value
    ElseIf IsEmpty(CurrentValue) Then
        ' Set default value
        CurrentValue = DefaultValue
    End If
    ' Return value.
    ThisValue = CurrentValue
End Function

To set a value:

ThisValue "Some text value"

To get the value:

CurrentValue = ThisValue

In a query:

ThisValue "SomeText"  ' Set value to filter on.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"

Using DoCmd.SetParameter

The uses of DoCmd.SetParameter are rather limited, so I'll be brief. It allows you to set a parameter for use in DoCmd.OpenForm, DoCmd.OpenReport and some other DoCmd statements, but it doesn't work with DoCmd.RunSQL, filters, DAO and ADO.

Sample implementation

DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"

Using DAO

In DAO, we can use the DAO.QueryDef object to create a query, set parameters, and then either open up a recordset or execute the query. You first set the queries' SQL, then use the QueryDef.Parameters collection to set the parameters.

In my example, I'm going to use implicit parameter types. If you want to make them explicit, add a PARAMETERS declaration to your query.

Sample implementation

'Execute query, unnamed parameters
With CurrentDb.CreateQueryDef("", "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ?p1 And Field2 = ?p2")
    .Parameters(0) = Me.Field1
    .Parameters(1) = Me.Field2
End With

'Open recordset, named parameters
Dim rs As DAO.Recordset
With CurrentDb.CreateQueryDef("", "SELECT Field1 FROM Table2 WHERE Field1 = FirstParameter And Field2 = SecondParameter")
    .Parameters!FirstParameter = Me.Field1 'Bang notation
    .Parameters("SecondParameter").Value = Me.Field2 'More explicit notation
    Set rs = .OpenRecordset
End With

While this is only available in DAO, you can set many things to DAO recordsets to make them use parameters, such as form recordsets, list box recordsets and combo box recordsets. However, since Access uses the text, and not the recordset, when sorting and filtering, those things may prove problematic if you do.

Using ADO

You can use parameters in ADO by using the ADODB.Command object. Use Command.CreateParameter to create parameters, and then append them to the Command.Parameters collection.

You can use the .Parameters collection in ADO to explicitly declare parameters, or pass a parameter array to the Command.Execute method to implicitly pass parameters.

ADO does not support named parameters. While you can pass a name, it's not processed.

Sample implementation:

'Execute query, unnamed parameters
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database
    .CommandText = "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ? And Field2 = ?"
    .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) 'adVarWChar for text boxes that may contain unicode
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, 8, Me.Field2) 'adInteger for whole numbers (long or integer)
End With

'Open recordset, implicit parameters
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database
    .CommandText = "SELECT Field1 FROM Table2 WHERE Field1 = @FirstParameter And Field2 = @SecondParameter"
     Set rs = .Execute(,Array(Me.Field1, Me.Field2))
End With

The same limitations as opening DAO recordsets apply. While this way is limited to executing queries and opening recordsets, you can use those recordsets elsewhere in your application.

Tuesday, June 1, 2021
If Sheets("Sheet1").OLEObjects("CheckBox1").Object.Value = True Then

I believe Tim is right. You have a Form Control. For that you have to use this

If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = 1 Then
Friday, July 23, 2021

This piece of ccode uses RmDir to remove the Folder. AFAIK, RmDir cannot delete the folder unless it is empty, so we first clear the content in the folder then remove the directory.

Private Sub PrepareDirModified(dirStr As String)
On Error Resume Next
    If Right(dirStr, 1) <> "" Then dirStr = dirStr & ""
    Kill dirStr & "*.*" 
    RmDir dirStr
    MkDir dirStr
On Error GoTo 0
End Sub

Hope this helps.

Saturday, July 31, 2021

Add the below code after For Each SubFolder In SourceFolder.SubFolders

Call List_XL_Files(SheetName, SubFolder.Path, True)

It will work

Saturday, August 28, 2021
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :