Making your sheet work as SQL database table?

Tags

Sub Test()
    Dim strFile As String
    strFile = Application.GetOpenFilename(“Excel workbooks,*.xls*”)
    ‘this gives a popo up browse option for user to select the excel file they want
    If strFile = “False” Then
        ‘ the user clicked Cancel
        ‘…
    Else
        Workbooks.Open strFile ‘now strfile is a variable declared above as string which stores the full path of the selected file by user
       

Dim Conn As New ADODB.Connection ‘creating variable for connection
Dim mrs As New ADODB.Recordset      ‘creating a recordset

Dim DBPath As String, sconnect As String

DBPath = strFile ‘DBpath stores the value from strFile
sconnect = “Provider=MSDASQL.1;DSN=Excel Files;DBQ=” & DBPath & “;HDR=Yes’;”

Conn.Open sconnect
sSQLSting = “SELECT  COUNT (*) From [General query for wlet error$] WHERE ACTVTST_CONDTN_CD IN (‘WACR01’)” ‘QL query to be fetched again your sheet

mrs.Open sSQLStingod, Conn
    ActiveSheet.Range(“AJ2”).CopyFromRecordset mrs ‘paste the recordseet values from the SQL query output
mrs.Close
Conn.Close
    End If
    End Sub

Advertisements

Excel VBA msgbox Function!!

Well good morning, so here is what msgbox dialog function in excel VBA does!!

Hmm..IF you are already familiar with the msgbox, you can probably answer the below Question?

What an msgbox does and when we use this in our program?

Here My Understanding of this …..

Msgbox is a function in excel VBA macro, which displays a dialog box with the predefined message on it (a message user want to display) and it also contains the buttons which user clicks after reading the message (a button clicked is the result of the user decision after reading the message) and your program converts the button press from the user to a integer value and progress based on it.

 

Woops,, it’s too much theory here; now let’s move to some practical stuff.

Here is the syntax of msgbox >>

Msgbox (text string [, buttons] [, title] [,helpfile , context])

Parts highlighted in green are mandatory, others are optional.

Here ‘Text_String’ is the message that you want the msgbox to display. The maximum length of ‘Text_String’ is 1024 characters.

‘buttons’ parameter specifies the type of buttons and icon that you want to be shown on the message box. It is an optional parameter. If you omit it then msgbox shows the default ‘vbOKOnly’ button.

‘title’ refers to the text displayed in the title bar of message box. This is an optional parameter.

‘helpfile’ is a string parameter that specifies the help file to be used for the dialog box. It is also an optional parameter but it becomes mandatory if ‘context’ parameter is to be used.

‘context’ is a numeric parameter that specifies the number assigned to the appropriate Help topic. It is an optional parameter but it becomes mandatory if ‘helpfile’ parameter is used.

Example1:

Basic message box.

 

Sub displayText()

‘this is a simple msgbox to display some message to user,remember only OK button is displayed to user

MsgBox “Welcome mr. Jack”

End Sub

 

Sub whichbutton()

‘this msgbox appears showing the user with the buttons which they can use as a chioce

MsgBox “Do you want to continue?”, vbYesNo

‘this msgbox appears showing the user a OK button (this can be done if you left the Button option blank)

MsgBox “Press Ok to continue”, vbOKOnly

‘this msgbox

End Sub

 

Constant Value Description
vbOKOnly 0 Display OK button only.
vbOKCancel 1 Display OK and Cancel buttons.
vbAbortRetryIgnore 2 Display Abort, Retry, and Ignore buttons.
vbYesNoCancel 3 Display Yes, No, and Cancel buttons.
vbYesNo 4 Display Yes and No buttons.
vbRetryCancel 5 Display Retry and Cancel buttons.
vbCritical 16 Display Critical Message icon.
vbQuestion 32 Display Warning Query icon.
vbExclamation 48 Display Warning Message icon.
vbInformation 64 Display Information Message icon.
vbDefaultButton1 0 First button is default.
vbDefaultButton2 256 Second button is default.
vbDefaultButton3 512 Third button is default.
vbDefaultButton4 768 Fourth button is default.
vbApplicationModal 0 Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal 4096 System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton 16384 Adds Help button to the message box
VbMsgBoxSetForeground 65536 Specifies the message box window as the foreground window
vbMsgBoxRight 524288 Text is right aligned
vbMsgBoxRtlReading 1048576 Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

 

1)creating a macro 2)message box 3)Objects 4)Variables 5)If then Statement 6)Loop 7)Macro Errors 8)String Manipulation 9)Date and Time 10)Events 11)Array 12)Function and sub 13)application Object 14)Active x controls 15)user form

Tags

, , , ,

Excel VBA (Visual basic for applications) is the name of the programming language of excel.

 

Below are the chapters we will cover during the chapter review of this session throughout the month.

 

1)creating a macro

2)message box

3)Objects

4)Variables

5)If then Statement

6)Loop

7)Macro Errors

8)String Manipulation

9)Date and Time

10)Events

11)Array

12)Function and sub

13)application Object

14)Active x controls

15)user form

Path and Fullname property in excel VBA

Tags

Path and Fullname property in excel VBA returns the saved path of the workbook.

 

Path property returns the path of the workbook without the workbook name where in Fullname property returns the path name including the workbook name in it.

 

See below how it works.

 

For this purpose I have created a folder named “test” in my D:\ drive, where I have created two macro enabled excel files named “macro practice.xlsm” and  “my workbook.xlsm”.

 

Now, I have opened the worksheet “macro practice.xlsm” and opened VBA editor by pressing Alt+F11 key (use can also right click on the sheet name and then view code option).

 

Code part:

 

Sub path()

‘declare variables to store the path name

Dim wbpath As String

Dim wbfullname As String

Dim wbfullname1 As String

Dim wbfullname2 As String

 

‘working with already open workbook ie;macro practice.xlsm

 

wbpath = Workbooks(“macro practice.xlsm”).path

MsgBox “you are now working with ” & wbpath

 

wbfullname = Workbooks(“macro practice.xlsm”).FullName

MsgBox “you are now working with fullname” & wbfullname

‘working with active workbook

‘I will now open my second workbook named “my workbook.xlsm” and activate both the already open workbooks_

‘one at a time and then try getting their fullname property

 

Workbooks.Open (“D:\test\my workbook.xlsm”)

Workbooks(“my workbook.xlsm”).Activate

 

wbfullname1 = ActiveWorkbook.FullName

MsgBox “your new opened wb name is ” & wbfullname1

 

‘swap the already open workbook (switch to macro practice.xlsm)

Workbooks(“macro practice.xlsm”).Activate

wbfullname2 = ActiveWorkbook.FullName

MsgBox “wb has been swapped and is ” & wbfullname2

 

Workbooks(“my workbook.xlsm”).Close

 

End Sub