Shared Name For the Drive

QueryDosDeviceW API Function can be used to get the device name.

To get the Shared Name of the drive, use the following function:

Public Function ConvertDrive2ServerName(ByVal sFullPath As String) As String


' --- Replaces the DriveName with ShareName in a given string

Dim FSO As FileSystemObject

Dim sDrive As String

Dim drvName As Drive

Dim sShare As String



On Error GoTo Err_Trap



Set FSO = New FileSystemObject



sDrive = FSO.GetDriveName(sFullPath)

Set drvName = FSO.GetDrive(sDrive)

sShare = drvName.ShareName



If LenB(sShare) <> 0 Then

ConvertDrive2ServerName = Replace(sFullPath, sDrive, sShare, 1, 1, vbTextCompare)

Else

ConvertDrive2ServerName = sFullPath

End If

If Not FSO Is Nothing Then Set FSO = Nothing



' ---------------------------------------

' Error Handling

' ---------------------------------------

Err_Trap:

If Err <> 0 Then

Err.Clear

Resume Next

End If

End Function

The function returns the DriveName with ShareName in a given string. It is advisable for programmers to store all the file locations using Sharename instead of DriveName

Cheers

Shasur

Get the Device Name (API Function)

Declare the API Function

Private Declare Function QueryDosDeviceW Lib "kernel32.dll" ( _ ByVal lpDeviceName As Long, _ ByVal lpTargetPath As Long, _ ByVal ucchMax As Long _ ) As Long Const MAX_PATH = 260

The following function accepts the drive as string and returns the device name

Public Function GetNtDeviceNameForDrive( _

ByVal sDrive As String) As String



Dim bDrive() As Byte

Dim bResult() As Byte

Dim lR As Long

Dim sDeviceName As String



If Right(sDrive, 1) = "\" Then

If Len(sDrive) > 1 Then

sDrive = Left(sDrive, Len(sDrive) - 1)

End If

End If

bDrive = sDrive



ReDim Preserve bDrive(0 To UBound(bDrive) + 2) As Byte

ReDim bResult(0 To MAX_PATH * 2 + 1) As Byte



lR = QueryDosDeviceW(VarPtr(bDrive(0)), VarPtr(bResult(0)), MAX_PATH)

If (lR > 2) Then

sDeviceName = bResult

sDeviceName = Left(sDeviceName, lR - 2)

GetNtDeviceNameForDrive = sDeviceName

End If



End Function


For example

Sub Trial()

MsgBox GetNtDeviceNameForDrive("c:")

End Sub

Dir Function in VBA (Visual Basic)

Dir or Dir$ Function can be used in Microsoft Visual Basic (Applications) to get the list of directories or files

sDir = Dir$(sPath & "*.xls", vbNormal)

Do Until LenB(sDir) = 0

Set oWB = Workbooks.Open(sPath & sDir)

‘ Do some stuff

oWB.close

sDir = Dir$

Loop

The above will open all Excel Workbooks under a particular directory.

Excel VBA - How to Know if the Cell Has Formula

To get the cell that contains formula couple of useful properties are available in Excel VBA

1. HasFormula
2. SpecialCells(xlCellTypeFormulas)

HasFormula will give True or False depending on the availabiliy of formulas

whereas

SpecialCells(xlCellTypeFormulas) can be used to select all the cells that contain formula

cells.SpecialCells(xlCellTypeFormulas).Select

or Set rng = cells.SpecialCells(xlCellTypeFormulas)

For each Cll in Rng

' Each cell containing formula

Next


Cheers
Shasur

Excel VBA InputBox

Excel VBA InputBox

InputBox method in Excel is more useful than the conventional inputBox functions when you require the user to select some range.

It is used as Application.InputBox

The Application.InputBox method differs from the usual InputBox function in that it allows selective validation of the user's input, and it can be used with Microsoft Excel objects, error values, and also formulas.

Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.

MyRange = Application.InputBox("Select the Range?","My Application")

Homerweb Search

MSFlexGrid datasource - Object variable not defined

When you try to use MSFlexGrid.datasource the error 'Object variable not defined; pops up.

Try using the combination of Heirarchial FlexGrid and ADO Data Control


Adodc1.RecordSource = "Select * From Table"

and

Set MSHFlexGrid1.DataSource = Adodc1

Now the FlexGrid will be populated with the data from the ADO Data Control. This is more useful as this can be modified at run-time.



Cheers
Shasur

Category: MSFlexGrid.datasource error

Faster Program Execution - Microsoft

Faster Program Execution is the dream of every developer. Here are some few tips for that. This is tested for Microsoft Visual Basic programs

Replace all Trim(), Left(), Right(), Mid() etc with Trim$(), Left$(), Right$(), Mid$() . These are string functions which will increase the speed

Also

If you are comparing if a string is empty

use

Len(Trim("STring")) = 0

rather than

Trim("STring") = ""

Even better would be

LenB(Trim$("STring")) = 0

as LenB() would be faster than Len().


More functions in coming articles...

Cheers
Shasur






Free Search Engine Submission

BreakLinks - Microsoft Excel

Use the following code to break the links to external source in Microsoft Excel Workbook


Sub Break_XL_Links()

Dim oXlLinks

oXlLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

MsgBox UBound(oXlLinks)

For i = 1 To UBound(oXlLinks)
MsgBox oXlLinks(i)
Next i

ActiveWorkbook.Breaklink Name:=oXlLinks(1), Type:=xlLinkTypeExcelLinks

End Sub

Removing External Links in Microsoft Excel SpreadSheet

Removing External Links in Excel SpreadSheet:

Some Microsoft Excel Spreadsheet will have links to other workbooks. If this needs to be broken use the following code:

Sub Remove_External_Links_in_Cells()

Dim LinkCell
Dim FormulaCells As Range
Dim SheetsInWorkbook As Object

For Each SheetsInWorkbook In Sheets
SheetsInWorkbook.Activate
'*** Error trapping if no formulacells found
On Error Resume Next
'*** Select only formula cells
Set FormulaCells = Cells.SpecialCells(xlFormulas)
'*** Loop every formulacell in sheet
For Each LinkCell In FormulaCells
'*** If you want paste linked value as "normal value"
MsgBox LinkCell.Parent.Name
If InStr(1, LinkCell.Value, ".xls]") = 0 Then
LinkCell.Value = LinkCell
End If
Next
Next
End Sub

Filler Function

Filler Function for Microsoft Excel

Function Filler(ByVal sBase As String, ByVal sFiller As String, ByVal iLen As Integer) As String

' ******** Filler **************************************
' Generic Function to stuff characters as prefix to a string
' ******************************************************

Dim i As Integer
If iLen > Len(sBase) Then
For i = Len(sBase) To iLen - 1
sBase = sFiller & sBase '* Stuff the string with specified filler characters
Next i
End If
Filler = sBase '* Return the stuffed string

End Function

This function can be used to fill equal number of spaces or fill zeroes for any string.


This can be used for Microsoft EXcel and also in Visual Basic PRograms