Thursday, December 14, 2006

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)


ConvertDrive2ServerName = sFullPath

End If

If Not FSO Is Nothing Then Set FSO = Nothing

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

' Error Handling

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


If Err <> 0 Then


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



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


sDir = Dir$


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


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


or Set rng = cells.SpecialCells(xlCellTypeFormulas)

For each Cll in Rng

' Each cell containing formula


free search engine website submission top optimization

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"


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.


Category: MSFlexGrid.datasource error

Friday, December 08, 2006

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


If you are comparing if a string is empty


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...


Free Search Engine Submission

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
'*** 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
End Sub

search engine submissionscholarships
online degrees, online colleges
scholarship information

online schools and colleges

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

Search Engine Submission & Optimization
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO Google Group