Date Statement

Date Statement

It Sets the current system date.

Syntax

Date = date

Note: For systems running Microsoft Windows 95, the required date specification must be a date from January 1, 1980 through December 31, 2099. For systems running Microsoft Windows NT, date must be a date from January 1, 1980 through December 31, 2079. For the Macintosh, date must be a date from January 1, 1904 through February 5, 2040.

Example

This example uses the Date statement to set the computer system date. In the development environment, the date literal is displayed in short date format using the locale settings of your code.

Dim MyDate MyDate = #February 12, 1985#    ' Assign a date. Date = MyDate    ' Change system date.

  You may like to visit :

How DATE Function works in VBA

DATE AND TIME Function / References in Excel

How DATE Function works in VBA

DATE Function

Date function returns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

Date, and if the calendar is Gregorian, Date$ behavior is unchanged by the Calendar property setting. If the calendar is Hijri, Date$ returns a 10-character string of the form mm-dd-yyyy, where mm (01-12), dd (01-30) and yyyy (1400-1523) are the Hijri month, day and year. The equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.

Example

Sub Date_fn()
Dim TodayDate
TodayDate = Date    ' TodayDate contains the current system date.
MsgBox ("Today's Date: " & TodayDate)
End Sub

Output:

 

Delete Multiple worksheets using VBA

Below is one more way to delete multiple worksheets except active worksheet using VBA.

 Assign below code to submit button:

Sub Del_Worksheets()

Dim Wrksheet As Worksheet

For Each Wrksheet In ThisWorkbook.Worksheets

If Wrksheet.Name <> ThisWorkbook.ActiveSheet.Name Then

Application.DisplayAlerts = False

Wrksheet.Delete

Application.DisplayAlerts = True

End If

Next Wrksheet

End Sub

Earlier approach :

How to delete multiple worksheets in one click using Macro :





 

VBA code to Hide & Unhide multiple Worsheets

Hiding worksheet manually is a tedious task when its more than one right ........No Worries here we have VBA code which HIDE and UNHIDE the multiple worksheets

HIDE :


Use the below highlighted code to hide the multiple worksheets:

Sub Hide_worksheet()

Dim Wrksheet As Worksheet

For Each Wrksheet In ThisWorkbook.Worksheets

If Wrksheet.Name <> ThisWorkbook.ActiveSheet.Name Then

Wrksheet.Visible = xlSheetHidden

End If

Next Wrksheet

End Sub

Output:

 UNHIDE:

If you have multiple worksheet hided and you want to open them than below is the VBA code to unhide the multiple worksheets .....

Sub unhide_worksheet()

Dim Wrksheet As Worksheet

For Each Wrksheet In ActiveWorkbook.Worksheets

Wrksheet.Visible = xlSheetVisible

Next Wrksheet

End Sub

 You may like to visit below :

How ForEach works in VBA
For.. Next Loop in VBA with example



 

How SQLRetrieve works in VBA

SQLRetrieve

SQLRetrieve retrieves all or part of the results from a previously executed query.

Before using SQLRetrieve, you must establish a connection with SQLOpen, execute a query with SQLExecQuery, and have the results pending.

This function is contained in the Xlodbc.xla add-in. Before use of this function, one must establish a reference to the add-in by using the References command.

Syntax

SQLRetrieve(ConnectionNum, DestinationRef, MaxColumns, MaxRows, ColNamesLogical, RowNumsLogical, NamedRngLogical, FetchFirstLogical)

Part	Description
ConnectionNum	Required. The unique connection ID returned by SQLOpen and for which you have pending query results that were generated by SQLExecQuery. If ConnectionNum is not valid, SQLExecQuery returns Error 2015.
DestinationRef	Optional. A Range object that specifies where the results should be placed. This function overwrites any values in the cells, without confirmation. If DestinationRef refers to a single cell, SQLRetrieve returns all the pending results in that cell and in the cells to the right of and below it. If DestinationRef is omitted, the bindings established by previous calls to SQLBind are used to return results. If no bindings exist for the current connection, SQLRetrieve returns Error 2023. If a particular result column hasn’t been bound and DestinationRef is omitted, the results are discarded.
MaxColumns	Optional. The maximum number of columns returned to the worksheet, starting at DestinationRef. If MaxColumns specifies more columns than are available in the result, SQLRetrieve places data in the columns for which data is available and clears the additional columns. If MaxColumns specifies fewer columns than are available in the result, SQLRetrieve discards the rightmost result columns until the results fit the specified size. The order in which the data source returns the columns determines column position. If MaxColumns is omitted, all the results are returned
MaxRows	Optional. The maximum number of rows to be returned to the worksheet, starting at DestinationRef. If MaxRows specifies more rows than are available in the results, SQLRetrieve places data in the rows for which data is available and clears the additional rows. If MaxRows specifies fewer rows than are available in the results, SQLRetrieve places data in the selected rows but doesn’t discard the additional rows. You can retrieve extra rows by using SQLRetrieve again and setting FetchFirstLogical to False. If MaxRows is omitted, all the rows in the results are returned.
ColNamesLogical	Optional. True to have the column names be returned as the first row of results. False or omitted to have the column names not be returned.
RowNumsLogical	Optional. Used only when DestinationRef is included in the function call. True to have the first column in the result set contain row numbers. False or omitted to have the row numbers not be returned. You can also retrieve row numbers by binding column 0 (zero) with SQLBind.
NamedRngLogical	Optional. True to have each column of the results be declared as a named range on the worksheet. The name of each range is the resulting column name. The named range includes only the rows that are returned with SQLRetrieve. The default value is False.
FetchFirstLogical	Optional. Allows you to request results from the beginning of the result set. If FetchFirstLogical is False, SQLRetrieve can be called repeatedly to return the next set of rows until all the result rows have been returned. When there are no more rows in the result set, SQLRequest returns 0 (zero). If you want to retrieve results from the beginning of the result set, set FetchFirstLogical to True. To retrieve additional rows from the result set, set FetchFirstLogical to False in subsequent calls. The default value is False.

SQLRetrieve Output Value

Ø  SQLRetrieve returns the number of rows in the result set.

Ø  If SQLRetrieve is unable to retrieve the results on the specified data source or if there are no results pending, it returns Error 2042. If no data is found, SQLRetrieve returns 0 (zero).

 

Remarks

Before calling SQLRetrieve, you must do the following:

1. Establish a connection with a data source by using SQLOpen.

2. Use the connection ID returned in SQLOpen to send a query with SQLExecQuery.

Example

This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order.

databaseName = "NorthWind" queryString = _     "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve chan, output, , , True SQLClose chan

 How ADO(ActiveX Data Objects)DB connection and Recordset work

How to connect to Teradata and extract DB using VBA

How to connect to Cisco Information Server



 

How SQLExecQuery function works in VBA

SQLExecQuery

SQLExecQuery executes a query on a data source with a connection that has been established with SQLOpen.

SQLExecQuery executes only the query. Use SQLRetrieve or SQLRetrieveToFile to get the results.

This function is contained in the Xlodbc.xla add-in. Before use of  this function, one  must establish a reference to the add-in by using the References command .

Syntax

SQLExecQuery(ConnectionNum, QueryText)

ü  ConnectionNum   Required. The unique connection ID returned by SQLOpen that identifies the data source you want to query.

ü  QueryText   Required. The query to be executed on the data source. The query must follow the SQL syntax guidelines for the specific driver.

SQLExecQuery Output:

The value returned by SQLExecQuery depends on the SQL statement, as shown in the following table.

SQL statement	Return value
SELECT	The number of columns in the result set
UPDATE, INSERT, or DELETE	The number of rows affected by the statement
Any other valid SQL statement	0 (zero)

     If SQLExecQuery is unable to execute the query on the specified data source, it returns Error 2042.

              If ConnectionNum isn’t valid, SQLExecQuery returns Error 2015.

Remarks

Before calling SQLExecQuery, you must establish a connection to a data source by using SQLOpen. The unique connection ID returned by SQLOpen is used by SQLExecQuery to send queries to the data source.

If you call SQLExecQuery using a previously used connection ID, any pending results on that connection are replaced by the new results.

Example

This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order.

databaseName = "Northwind" queryString = _     "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve chan, output, , , True SQLClose chan

How ADO(ActiveX Data Objects)DB connection and Recordset work
How to connect to Teradata and extract DB using VBA
How to connect to Cisco Information ServerHow SQLOpen works in VBA  How SQLClose works in VBA

How SQLClose works in VBA

SQLClose

SQLClose closes a connection to an external data source.This function is contained in the Xlodbc.xla add-in.

Before use of this  function, one must establish a reference to the add-in by using the References command .

Syntax

SQLClose(ConnectionNum)

ConnectionNum   Required. The unique connection ID of the data source you want to disconnect from.

Output -Value

If the connection is successfully closed, this function returns 0 (zero) and the connection ID is no longer valid.

If ConnectionNum is not valid, this function returns Error 2015.
If SQLClose is unable to disconnect from the data source, it returns Error 2042.

Example

This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order.

databaseName = "Northwind" queryString = _     "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve chan, output, , , True SQLClose chan

 How ADO(ActiveX Data Objects)DB connection and Recordset work

How to connect to Teradata and extract DB using VBA

How to connect to Cisco Information Server

How SQLOpen works in VBA

 

SQLOpen

SQLOpen establishes a connection to a data source.This function is contained in the Xlodbc.xla add-in.

Before use of this function, one must establish a reference to the add-in by using the References command .

Syntax:

SQLOpen(ConnectionStr, OutputRef, DriverPrompt)

ConnectionStr   Required. Supplies the information required by the driver being used to connect to a data source; must follow the driver's format. ConnectionStr supplies the data source name and other information, such as user ID and passwords, that the driver requires to make a connection. You must define the data source name (DSN) used in ConnectionStr before you try to connect to it.

OutputRef   Optional. A Range object (must be a single cell) that contains the completed connection string. Use OutputRef when you want SQLOpen to return the completed connection string to a worksheet.

DriverPrompt   Optional. Specifies whether the driver dialog box is displayed and, if it is, which options are available in it. Use one of the values described in the following table. If DriverPrompt is omitted, SQLOpen uses 2 as the default.

List of Values:

 

Value	Meaning
1	The driver dialog box is always displayed.
2	The driver dialog box is displayed only if information provided by the connection string and the data source specification aren’t sufficient to complete the connection. All dialog box options are available.
3	The same as 2 except that dialog box options that aren’t required are dimmed (unavailable).
4	The driver dialog box isn’t displayed. If the connection isn’t successful, SQLOpen returns an error.

 

SQLOpen Output Values :

If successful, SQLOpen returns a unique connection ID number. Use the connection ID number with the other ODBC functions.

If SQLOpen is unable to connect using the information you provide, it returns Error 2042. Additional error information is placed in memory for use by SQLError.

Example:

This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order.

DatabaseName = "Northwind" QueryString = _     "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" Chan = SQLOpen("DSN=" & DatabaseName) SQLExecQuery Chan, QueryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve Chan, Output, , , True SQLClose Chan

you may like to visit below :

 How ADO(ActiveX Data Objects)DB connection and Recordset work

How to connect to Teradata and extract DB using VBA

How to connect to Cisco Information Server