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

Automating Excel in ASP.Net

Automating Excel or Word (or any Office product) is not recommended:

Problems using Automation of Office server-side

Developers who try to use Office in a server-side solution need to be aware of five major concerns in which Office behaves differently than anticipated because of the environment. If your code is to run successfully, these concerns need to be addressed and their effects minimized as much as possible. Consider these items carefully when you build your application because no one solution can address all of them, and different designs require you to prioritize the elements differently.
1.
User Identity: Office Applications assume a user identity when they are run, even when they are started by Automation. They attempt to initialize toolbars, menus, options, printers, and some add-ins based on settings in the user registry hive for the user who launches the application. Many services run under accounts that have no user profiles (such as the SYSTEM or IWAM_[servername] accounts), and therefore Office may fail to initialize properly on startup, returning an error on CreateObject or CoCreateInstance. Even if the Office application can be started, without a user profile other functions may fail to work properly. If you plan to Automate Office from a service, you need to configure either your code or Office so that it will run with a loaded user profile.
2.
Interactivity with the Desktop: Office Applications assume that they are being run under an interactive desktop, and may in some circumstances need to be made visible for certain Automation functions to work properly. If an unexpected error occurs, or an unspecified parameter is needed to complete a function, Office is designed to prompt the user with a modal dialog box that asks the user what they want to do. A modal dialog box on a non-interactive desktop cannot be dismissed, which causes that thread to stop responding (hang) indefinitely. Although certain coding practices can help reduce the likelihood of this occurring, they cannot prevent it entirely. This fact alone makes running Office Applications from a server-side environment risky and unsupported.
3.
Reentrancy and Scalability: Server-side components need to be highly reentrant, multi-threaded COM components with minimum overhead and high throughput for multiple clients. Office Applications are in almost all respects the exact opposite. They are non-reentrant, STA-based Automation servers that are designed to provide diverse but resource-intensive functionality for a single client. They offer little scalability as a server-side solution, and have fixed limits to important elements, such as memory, which cannot be changed through configuration. More importantly, they use global resources (such as memory mapped files, global add-ins or templates, and shared Automation servers), which can limit the number of instances that can run concurrently and lead to race conditions if they are configured in a multi-client environment. Developers who plan to run more then one instance of any Office Application at the same time need to consider "pooling" or serializing access to the Office Application to avoid potential deadlocks or data corruption.
4.
Resiliency and Stability: Office 2000, Office XP, and Office 2003 use Microsoft Windows Installer (MSI) technology to make installation and self-repair easier for an end user. MSI introduces the concept of "install on first use", which allows features to be dynamically installed or configured at runtime (for the system, or more often for a particular user). In a server-side environment this both slows down performance and increases the likelihood that a dialog box may appear that asks for the user to approve the install or provide an appropriate install disk. Although it is designed to increase the resiliency of Office as an end-user product, Office's implementation of MSI capabilities is counterproductive in a server-side environment. Furthermore, the stability of Office in general cannot be assured when run server-side because it has not been designed or tested for this type of use. Using Office as a service component on a network server may reduce the stability of that machine, and as a consequence your network as a whole. If you plan to automate Office server-side, attempt to isolate the program to a dedicated computer that cannot affect critical functions, and that can be restarted as needed.
5.
Server-Side Security: Office Applications were never intended for use server-side, and therefore do not take into consideration the security problems that are faced by distributed components. Office does not authenticate incoming requests, and does not protect you from unintentionally running macros, or starting another server that might run macros, from your server-side code. Do not open files that are uploaded to the server from an anonymous Web! Based on the security settings that were last set, the server can run macros under an Administrator or System context with full privileges and compromise your network! In addition, Office uses many client-side components (such as Simple MAPI, WinInet, MSDAIPP) that can cache client authentication information in order to speed up processing. If Office is being automated server-side, one instance may service more than one client, and because authentication information has been cached for that session, it is possible that one client can use the cached credentials of another client, and thereby gain non-granted access permissions by impersonating other users.


(courtesy: http://support.microsoft.com/default.aspx?scid=kb;EN-US;257757)

ASP.Net Tips & Tricks http://aspdud.blogspot.com/

ADO connection string for Excel

If are a diehard ADO user. Here is the connection string for Excel

sXL = "c:\DaniWebExample.xls"
Set Cn = New ADODB.ConnectionCn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sXL &amp; ";Extended Properties=Excel 8.0;Persist Security Info=False"Cn.ConnectionTimeout = 40Cn.Open

The rest is the usual ADO recordset retrieving technique

Using MS Excel as Database : ADODB

If are a diehard ADO user. Here is the connection string for Excel

sXL = "c:\DaniWebExample.xls"

Set Cn = New ADODB.ConnectionCn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & sXL &amp; ";Extended Properties=Excel 8.0;Persist Security Info=False"Cn.ConnectionTimeout = 40

Cn.Open

The rest is the usual ADO recordset retrieving technique

VBA - Creating PowerPoint Presentation

Most often we will come across a scenario where powerpoint slides need to be created automatically.

Here is a sample & simple code to do that. This code is created using VBA (Excel 2000)

Sub Create_PowerPoint_Slides()

On Error GoTo Err_PPT

Dim oPA As PowerPoint.Application
Dim oPP As PowerPoint.Presentation
Dim oPS As PowerPoint.Slide
Dim oShape As PowerPoint.Shape
Dim sPath As String
Dim sFile As String
Dim i1 As Integer


sPath = "C:\"
sFile = "MyfileName"

Set oPA = New PowerPoint.Application
oPA.Visible = msoTrue

Set oPP = oPA.Presentations.Add(msoTrue)

For i1 = 1 To 10
oPP.Slides.Add 1, ppLayoutBlank
Next i1

Set oPS = oPP.Slides(1)
Set oShape = oPS.Shapes.AddTextbox(msoTextOrientationHorizontal, 140#, 246#, 400#, 36#)
oShape.TextFrame.WordWrap = msoTrue

oShape.TextFrame.TextRange.Text = "Comments For File : " & sFile
With oShape
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.RGB = RGB(204, 255, 255)
.Line.Weight = 3#
.Line.Visible = msoTrue
.Line.ForeColor.SchemeColor = ppForeground
.Line.BackColor.RGB = RGB(255, 255, 255)
End With


oPP.SaveAs sPath & sFile & ".ppt"
oPP.Close
oPA.Quit

If Not oPS Is Nothing Then Set oPS = Nothing
If Not oPP Is Nothing Then Set oPP = Nothing
If Not oPA Is Nothing Then Set oPA = Nothing



Err_PPT:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
Resume Next
End If

End Sub

Extracting Keywords -- Code

Sub GetKeyWords()

Dim oWA As Word.Application
Dim oWD As Word.Document

Set oWD = Documents("c:\Sample.Doc")

sKeyWords = oWD.BuiltinDocumentProperties("Keywords").Value

If not oWD is nothing then set oWD = nothing
If not oWA is nothing then set oWA = nothing

End Sub

Need Business Long Distance, Voice PRI, and Business VOIP service? Compare free Long Distance Price Quotes from over 30 providers!

SQL Server 2005 - Remote Connection Part I

If you have downloaded the express edition, you may not connect to the server from a remote machine.

In the SQL Server Configuration Manager (SSCM) enable the Remote Connections Option

Under the SQL Native Client Configuration enable TCP/IP protocol (see Figure below)

Welcome to VBA Tips & Tricks

Hi All

Welcome to VBA Tips & Tricks.

All VBA related information will be posted on this blog. Of late, VBA has been disregarded by many software professionals for .Net, c# and other technologies. This blog will also post articles related to them too

Happy reading