Posts tagged VBA

Access VBA: Link all Dbase files from a folder

Jul18
2007 Leave a Comment Written by admin

If you would like to link all Dbase files, any linkable file for that matter, in MS Access, use the following code. I read somewhere that refreshing the links is slower than deleting and creating new links.

Sub LinkAllTblsinDir()
Dim sTblNm As String, sPath As String, sFileNm As String
sPath = “C:\DW\”
‘Turn of the Echo to avoid window repaint/refresh
Application.Echo False

sFileNm = Dir(sPath, vbNormal)
Do While sFileNm “”
If Right(sFileNm, 3) = “dbf” Then
sTblNm = Left(sFileNm, Len(sFileNm) – 4) ‘Extract the file name
‘Use the TransferDatabase option to link the tables from the specified directory
‘to your current Access DB
DoCmd.TransferDatabase acLink, “dBase III”, sPath, acTable, sTblNm, sTblNm
End If
sFileNm = Dir
Loop

Application.Echo True
End Sub

Posted in Access, Useful Procedures - Tagged ,

Get file names from a directory

Jul18
2007 Leave a Comment Written by admin

If you want get or print file names from a certain directory, then you can use following code.


Sub GetFileNames()
Dim sPath As String, sFileNm As String

sPath = “C:\DW\”
‘You can also use Application.GetOpenFilename to get a file name from a folder,
‘and then extract the Directory name from that string
‘You can also use filters with GetOpenFilenam such as *.txt, see Help on this topic

sFileNm = Dir(sPath, vbNormal) ‘Get the first file from the specified directory
‘Start a loop
Do While sFileNm “”
‘If the file has a dbf extension then print the file name
If Right(sFileNm, 3) = “dbf” Then
Debug.Print sFileNm
End If
sFileNm = Dir
Loop
End Sub

Posted in Useful Procedures

Range Concatenation with a character

Jun29
2007 Leave a Comment Written by admin

Are you frustrated that you have to concatenate a range, and you have to do that using CONCATENATE formula by entering each cell and typing a comma after every cell? Well, here’s a solution to it. A procedure or a function whatever you like. If you use the procedure, it allows you to choose the input range, concatenate character, and the output range. If you use the function, then you can enter the optional concatenate character (by default it is a comma (,)), and the input range.

Here are both:

Procedure:

Public Sub ConCatwChar()
Dim sChar2bAdded As String, rngRng2bCated As Range, sOutput As String, rngTarget As Range, c As Range
On Error GoTo ConCatwChar_Error
‘You could use this line to return the concatenated string in this cell
‘Set rngTarget = ActiveCell

Set rngRng2bCated = Application.InputBox(prompt:=”Select the range you’d like to concatenate with a charcter”, _
Title:=”Select Range”, Type:=8)

If rngRng2bCated Is Nothing Then Exit Sub

‘You could use this line to set the default to a comma and remove the inputbox line
‘sChar2bAdded = “,”
sChar2bAdded = InputBox(“Enter the character you’d like to add between other cells”, “Enter Character”, “,”)

Set rngTarget = Application.InputBox(prompt:=”Select the range you’d like the output”, _
Title:=”Select Range”, Type:=8)

For Each c In rngRng2bCated
sOutput = sOutput & c.Value & sChar2bAdded
Next c

sOutput = Left(sOutput, Len(sOutput) – Len(sChar2bAdded))
rngTarget = sOutput

On Error GoTo 0
Exit Sub

ConCatwChar_Error:
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure ConCatwChar”
End Sub

Here’s the function:

‘You can change the option string character to nothing “” so that you get concatenated string without a character in between
Public Function ConCatFunc(rngRng2bCated As Range, Optional sChar2bAdded As String = “,”) As String
Dim sOutput As String, c As Range

On Error GoTo ConCatFunc_Error

For Each c In rngRng2bCated
sOutput = sOutput & c.Value & sChar2bAdded
Next c
sOutput = Left(sOutput, Len(sOutput) – Len(sChar2bAdded))
ConCatFunc = sOutput

On Error GoTo 0
Exit Function

ConCatFunc_Error:
ConCatFunc = “#Error#”

End Function

Posted in String Operations, Useful Procedures - Tagged ,

MOD Function and Mod Operator Return Different Values

Feb08
2006 Leave a Comment Written by admin

I didn’t know this before today when an user at MrExcel wondered why he was not getting consistent answers with Excel and VBA. The key is negative numbers.

If you happen to have negative numbers (one of them) and you want to calculate MOD, you should use this code in VBA

yourmodval=n-d*INT(n/d)

Where n is your number and d is your divisor.

For more info, check:
MOD Function and Mod Operator Return Different Values

Posted in Uncategorized - Tagged ,

Change references

Feb01
2006 2 Comments Written by admin

If you want to change references of a formula, you could toggle F4 key inside the formula to change the references from Absolute, Column Absolute Row Absolute or all relative.

Although, if you have to change references from a range, you can use these two options
1. Download ASAP utilities, because it’s worth it, they have more than 300 utilities which are used many times.

2. Use this code.

Sub Change_refs()
‘This sub will change references of formula to different options shown by a userform
‘This code will not run without the userform
On Error GoTo Change_refs_Error
Load frmRefType
frmRefType.Show
Dim IPFormula As String, OPFormula As String, c As Range
Dim RefStyle As XlReferenceStyle
With frmRefType
    If .obAbs Then
        RefStyle = xlAbsolute
    ElseIf .obCAbs Then
        RefStyle = xlRelRowAbsColumn
    ElseIf .obRAbs Then
        RefStyle = xlAbsRowRelColumn
    ElseIf .obRel Then
        RefStyle = xlRelative
    Else
        Exit Sub
    End If
End With
Application.ScreenUpdating = False
For Each c In Selection
    If IsEmpty(c) Then GoTo Nextc
    IPFormula = c.Formula
    OPFormula = Application.ConvertFormula( _
        Formula:=IPFormula, _
        fromReferenceStyle:=xlA1, _
        toReferenceStyle:=xlA1, ToAbsolute:=RefStyle)
    c.Formula = OPFormula
Nextc:
Next c
Application.ScreenUpdating = True
Unload frmRefType
   On Error GoTo 0
   Exit Sub
Change_refs_Error:
    If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
    Unload frmRefType
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure Change_refs”
End Sub

You’ll have to download the add-in to make this code run. You can edit the Addin just by opening it in VBA. Here is the Addin. If you install this add in it will create a button “Change References”

Posted in Uncategorized - Tagged
« Older Entries Newer Entries »