VBA Code
HTML
Excel offers several functions for exporting Excel objects in HTML format as well as allowing you to import data from HTMl files.
These functions can be run using the program code with the objects PublishObject (exporting) and WebQuery (importing)
Web Components
These are controls that are suitable for the Internet in which an Excel table can be represented.
While not all Excel functions are available manu are and the advantage is that data can be manipulated dynamically in the HTML document.
There are a few diadvantages though as the web components is only available if you have a licensed copy of Office on the PC.
Good for intranets when you know what is on all the PC's.
Workbook.EnvelopeVisible property you can insert various text fields for the input of e-mail addresses and subject lines in the upper border of the table window.
Sending Excel files as e-mail
ThisWorkbook.SendMail(Recipients, Subject, ReturnReceipt)
Recipients Required Variant. Specifies the name of the recipient as text, or as an array of text strings if there are multiple recipients. At least one recipient must be specified, and all recipients are added as To recipients.
Subject Optional Variant. Specifies the subject of the message. If this argument is omitted, the document name is used.
ReturnReceipt Optional Variant. True to request a return receipt. False to not request a return receipt. The default value is False.
The following line of code will send the activeworkbook without any prompts or confirmations.
ActiveWorkbook.SendMail Recipients:="Jean Selva"
If you want to send an email to several recipients simultaneously you can pass an array of character strings in the Recipients parameter.
You do not need to save the workbook first before sending it.
SendMail can only be used when Application.MailSystem = xlMAPI
Determining the Type of Installed Mail System
Application.MailSystem
Returns the mail system that's installed on the host machine. Read-only XlMailSystem.
xlMAPI
Select Case Application.MailSystem
Case xlMAPI
MsgBox "Mail system is Microsoft Mail"
Case xlPowerTalk
MsgBox "Mail system is PowerTalk"
Case xlNoMailSystem
MsgBox "No mail system installed"
End Select
Sending Files Interactively
If you want the user to have an element of control over the email they are sending you have 2 options:
Display a separate dialog box.
Application.Dialogs(xlDialogSendMail).Show
Use the built-in Envelope
ThisWorkbook.EnveloperVisible = Not ThisWorkbook.EnveloperVisible
Connections
Sub ChangeSQL()
Dim dbConnections As Excel.Connections
Dim dbWorkbookConnection As Excel.WorkbookConnection
Dim dbOLEDBConnection As Excel.OLEDBConnection
Set dbConnections = ActiveWorkbook.Connections
Set dbWorkbookConnection = dbConnections.Item("Northwind")
Set dbOLEDBConnection = dbWorkbookConnection.OLEDBConnection
dbOLEDBConnection.CommandText = "SELECT * FROM Orders"
dbWorkbookConnection.Refresh
End Sub
If you change just the order of the select columns the change is not always refrected when you refresh
QueryTable
Represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database.
The QueryTable object is a member of the QueryTables collection.
Use QueryTables(index), where index is the index number of the query table, to return a single QueryTable object.refreshed.
The following example sets query table one so that formulas to the right of it are automatically updated whenever it's
Sheets("sheet1").QueryTables(1).FillAdjacentFormulas = True
QueryType property
Indicates the type of query used by Microsoft Excel to populate the query table or PivotTable cache. Read-only XlQueryType.
You specify the data source in the prefix for the Connection property's value.
Sheets("sheet1").QueryTables(1).QueryType = xlQueryType.xlADORecordset
Different Types of Query
ADO Recordset
DAO Recordset
ODBC Query
OLE DB Query
Text Import
Web Query
Sub Add_QueryTable()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
With wsSheet
Set rnStart = .Range("A1")
End With
stSQL = "SELECT * FROM Shippers;"
Set cnt = New ADODB.Connection
With cnt
.CursorLocation = adUseClient
.Open stADO
Set rst = .Execute(stSQL)
End With
'Here we add the Recordset to the QueryTable.
Set qData = wsSheet.QueryTables.Add(rst, rnStart)
'In order to view any data the QueryTable need to
'be refreshed.
qData.Refresh
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
AdjustColumnWidth property
True if the column widths are automatically adjusted for the best fit each time you refresh the specified query table or XML map.
False if the column widths aren't automatically adjusted with each refresh. The default value is True. Read/write Boolean.
The maximum column width is two-thirds the width of the screen.
This example turns off automatic column-width adjustment for the newly added query table on the first worksheet in the first workbook.
With Workbooks(1).Worksheets(1).QueryTables _
.Add(Connection:=varDBConnStr, _
Destination:=Range("B1"), _
Sql:="Select Price From CurrentStocks " & _
"Where Symbol = 'MSFT'")
.AdjustColumnWidth = False
.Refresh
End With
BackgroundQuery property
True if queries for the PivotTable report or query table are performed asynchronously (in the background). Read/write Boolean.
For OLAP data sources, this property is read-only and always returns False.
This example causes queries for the first PivotTable report on worksheet one to be performed in the background.
Worksheets(1).PivotTables("Pivot1").PivotCache.BackgroundQuery = True
CancelRefresh Method
Cancels all background queries for the specified query table. Use the Refreshing property to determine whether a background query is currently in progress.
Delete Method
Deletes the object.
Destination property
Returns the cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTable object. Read-only Range.
This example scrolls through the active window until the upper-left corner of query table one is in the upper-left corner of the window.
Set d = Worksheets(1).QueryTables(1).Destination
With ActiveWindow
.ScrollColumn = d.Column
.ScrollRow = d.Row
End With
EnableEditing property
True if the user can edit the specified query table. False if the user can only refresh the query table. Read/write Boolean.
EnableRefresh property
True if the PivotTable cache or query table can be refreshed by the user. The default value is True. Read/write Boolean.
The RefreshOnFileOpen property is ignored if the EnableRefresh property is set to False.
For OLAP data sources, setting this property to False disables updates.
FetchedRowOverflow property
True if the number of rows returned by the last use of the Refresh method is greater than the number of rows available on the worksheet. Read-only Boolean.
With Worksheets(1).QueryTables(1)
.Refresh
If .FetchedRowOverflow Then
MsgBox "Query too large: please redefine."
End If
End With
FieldNames property
True if field names from the data source appear as column headings for the returned data. The default value is True. Read/write Boolean.
FillAdjacentFormulas property
True if formulas to the right of the specified query table are automatically updated whenever the query table is refreshed. Read/write Boolean.
ListObject property
Returns a ListObject object for the Range object or QueryTable object. Read-only ListObject object.
MaintainConnection property
True if the connection to the specified data source is maintained after the refresh and until the workbook is closed. The default value is True. Read/write Boolean.
You can set the MaintainConnection property only if the QueryType property of the query table or PivotTable cache is set to xlOLEDBQuery.
If you anticipate frequent queries to a server, setting this property to True might improve performance by reducing reconnection time. Setting the property to False causes an open connection to be closed.
Name Property
Returns or sets the name of the object. Read/write String.
Parameters property
Returns a Parameters collection that represents the query table parameters. Read-only.
PreserveColumnInfo property
True if column sorting, filtering, and layout information is preserved whenever a query table is refreshed. The default value is False. Read/write Boolean.
This property has an effect only when the query table is using a database connection.
You can set this property to False for compatibility with earlier versions of Microsoft Excel.
PreserveFormatting property
this property is True if any formatting common to the first five rows of data are applied to new rows of data in the query table. Unused cells aren't formatted. The property is False if the last AutoFormat applied to the query table is applied to new rows of data. The default value is True (unless the query table was created in Microsoft Excel 97 and the HasAutoFormat property is True, in which case PreserveFormatting is False).
For database query tables, the default formatting setting is xlSimple.
The new AutoFormat style is applied to the query table when the table is refreshed. The AutoFormat is reset to None whenever PreserveFormatting is set to False. As a result, any AutoFormat that's set before PreserveFormatting is set to False and before the query table is refreshed doesn't take effect, and the resulting query table has no formatting applied to it.
This example demonstrates how setting PreserveFormatting to False causes the AutoFormat to be set to xlRangeAutoFormatNone instead of the specified xlRangeAutoFormatColor1 format.
With Workbooks(1).Worksheets(1).QueryTables(1)
.Range.AutoFormat = xlRangeAutoFormatColor1
.PreserveFormatting = False
.Refresh
End With
Recordset property
Returns or sets a Recordset object that's used as the data source for the specified query table or PivotTable cache. Read/write.
If this property is used to overwrite an existing recordset, the change takes effect when the Refresh method is run.
Refresh Method
Updates an external data range (QueryTable). Boolean.
expression.Refresh(BackgroundQuery)
BackgroundQuery Optional Variant. Used only with QueryTables that are based on the results of a SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted. The QueryTable is updated in the background. False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn't specified, the setting of the BackgroundQuery property determines the query mode.
The following remarks apply to QueryTable objects that are based on the results of a SQL query.
The Refresh method causes Microsoft Excel to connect to the data source of the QueryTable object, execute the SQL query, and return data to the range that is based on the QueryTable object. Unless this method is called, the QueryTable object doesn't communicate with the data source.
When making the connection to the OLE DB or ODBC data source, Microsoft Excel uses the connection string specified by the Connection property. If the specified connection string is missing required values, dialog boxes will be displayed to prompt the user for the required information. If the DisplayAlerts property is False, dialog boxes aren't displayed and the Refresh method fails with the Insufficient Connection Information exception.
After Microsoft Excel makes a successful connection, it stores the completed connection string so that prompts won't be displayed for subsequent calls to the Refresh method during the same editing session. You can obtain the completed connection string by examining the value of the Connection property.
After the database connection is made, the SQL query is validated. If the query isn't valid, the Refresh method fails with the SQL Syntax Error exception.
If the query requires parameters, the Parameters collection must be initialized with parameter binding information before the Refresh method is called. If not enough parameters have been bound, the Refresh method fails with the Parameter Error exception. If parameters are set to prompt for their values, dialog boxes are displayed to the user regardless of the setting of the DisplayAlerts property. If the user cancels a parameter dialog box, the Refresh method halts and returns False. If extra parameters are bound with the Parameters collection, these extra parameters are ignored.
The Refresh method returns True if the query is successfully completed or started; it returns False if the user cancels a connection or parameter dialog box.
To see whether the number of fetched rows exceeded the number of available rows on the worksheet, examine the FetchedRowOverflow property. This property is initialized every time the Refresh method is called.
This example refreshes the PivotTable cache for the first PivotTable report on the first worksheet in a workbook.
Worksheets(1).PivotTables(1).PivotCache.Refresh
Refreshing property
True if there's a background query in progress for the specified query table. Read/write Boolean.
Use the CancelRefresh method to cancel background queries.
RefreshOnFileOpen property
True if the PivotTable cache or query table is automatically updated each time the workbook is opened. The default value is False. Read/write Boolean.
Query tables and PivotTable reports are not automatically refreshed when you open the workbook by using the Open method in Visual Basic. Use the Refresh method to refresh the data after the workbook is open.
RefreshPeriod property
Returns or sets the number of minutes between refreshes. Read/write Long.
Setting the period to 0 (zero) disables automatic timed refreshes and is equivalent to setting this property to Null.
The value of the RefreshPeriod property can be an integer from 0 through 32767.
RefreshStyle property
Returns or sets the way rows on the specified worksheet are added or deleted to accommodate the number of rows in a recordset returned by a query. Read/write XlCellInsertionMode.
ResultRange property
Returns a Range object that represents the area of the worksheet occupied by the specified query table. Read-only.
The range doesn't include the field name row or the row number column.
RobustConnect property
Returns or sets how the PivotTable cache connects to its data source. Read/write XlRobustConnect.
RowNumbers property
True if row numbers are added as the first column of the specified query table. Read/write Boolean.
Setting this property to True doesn't immediately cause row numbers to appear. The row numbers appear the next time the query table is refreshed, and they're reconfigured every time the query table is refreshed.
SaveData property
True if data for the PivotTable report is saved with the workbook. False if only the report definition is saved. Read/write Boolean.
For OLAP data sources, this property is always set to False.
SavePassword property
True if password information in an ODBC connection string is saved with the specified query. False if the password is removed. Read/write Boolean.
This property affects only ODBC queries.
SourceConnectionFile property
Returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the PivotTable. Read/write.
EditWebPage property
Returns or sets the web page Uniform Resource Locator (URL) for a web query. Read/write Variant.
The EditWebPage property returns Null if not set. The EditWebPage property is only meaningful if the query type is Web or OLE.
If the EditWebPage is not null then ignore the WebTables property for refreshing. As a result an XML query and the WebTable property refers to the table in the original Web page and should only be used in the edit case to pre-populate the Web Query dialog box.
In this example, Microsoft Excel displays to the user a Web page URL. This example assumes a QueryTable object in cell A1 exists in the active worksheet and that a file called "MyHomepage.htm" exists on the C: drive.
Sub ReturnURL()
' Set the EditWebPage property to a source.
Range("A1").QueryTable.EditWebPage = "C:\MyHomepage.htm"
' Display the source to the user.
MsgBox Range("A1").QueryTable.EditWebPage
End Sub
WebConsecutiveDelimitersAsOne property
True if consecutive delimiters are treated as a single delimiter when you import data from HTML <PRE> tags in a Web page into a query table, and if the data is to be parsed into columns.
False if you want to treat consecutive delimiters as multiple delimiters. The default value is True. Read/write Boolean.
Use this property only when the query table's QueryType property is set to xlWebQuery, the query returns an HTML document, and the WebPreFormattedTextToColumns property is set to True.
WebDisableDateRecognition property
True if data that resembles dates is parsed as text when you import a Web page into a query table. False if date recognition is used. The default value is False. Read/write Boolean.
Use this property only when the query table's QueryType property is set to xlWebQuery and the query returns an HTML document.
WebDisableRedirections property
True if Web query redirections are disabled for a QueryTable object. The default value is False. Read/write Boolean.
WebFormatting property
Returns or sets a value that determines how much formatting from a Web page, if any, is applied when you import the page into a query table. Read/write XlWebFormatting.
Use this property only when the query table's QueryType property is set to xlWebQuery and the query returns an HTML document.
WebPreFormattedTextToColumns property
Returns or sets whether data contained within HTML <PRE> tags in the Web page is parsed into columns when you import the page into a query table. The default is True. Read/write Boolean.
This property is used only when the QueryType property of the query table is xlWebQuery and the query returns a HTML document.
WebSelectionType property
Returns or sets a value that determines whether an entire Web page, all tables on the Web page, or only specific tables on the Web page are imported into a query table. Read/write XlWebSelectionType.
Use this property only when the query table's QueryType property is set to xlWebQuery and the query returns an HTML document.
If the value of this property is xlSpecifiedTables, you can use the WebTables property to specify the tables to be imported.
WebSingleBlockTextImport property
True if data from the HTML <PRE> tags in the specified Web page is processed all at once when you import the page into a query table. False if the data is imported in blocks of contiguous rows so that header rows will be recognized as such. The default value is False. Read/write Boolean.
Use this property only when the query table's QueryType property is set to xlWebQuery and the query returns an HTML document.
WebTables property
Returns or sets a comma-delimited list of table names or table index numbers when you import a Web page into a query table. Read/write String.
Use this property only when the query table's QueryType property is set to xlWebQuery, the query returns an HTML document, and the value of the WebSelectionType property is xlSpecifiedTables.
Web Queries
References of the form QueryTable.Name = "abc" are not properly executed
If there was previously a querytable with the name "abc" then a new querytable is designated by "abc_1", "abc_2" and so on.
Even if the old query table was deleted a long time ago.
What are QueryTables ?
Returns the QueryTables collection that represents all the query tables on the specified worksheet. Read-only.
Each QueryTable object represents a worksheet table built from data returned from an external data source.
Refreshing
This example refreshes all query tables on worksheet one.
For Each qt in Worksheets(1).QueryTables
qt.Refresh
Next
FillAdjacentFormulas
This example sets query table one so that formulas to the right of it are automatically updated whenever it's refreshed.
Sheets("sheet1").QueryTables(1).FillAdjacentFormulas = True
QueryType
Indicates the type of query used by Microsoft Excel to populate the query table or PivotTable cache. Read-only XlQueryType .
expression.QueryType = xlQueryType.xlWebQuery.
Connection
You specify the data source in the prefix for the Connection property's value.
Set qtQtrResults = _
Workbooks(1).Worksheets(1).QueryTables(1)
With qtQtrResults
if .QueryType = xlWebQuery Then
.Refresh
End If
End With
Inserting a New WebQuery
Sub CreateNewQuery()
Dim WSD As Worksheet
Dim WSW As Worksheet
Dim QT As QueryTable
For m = 1 To 27
Select Case m
Case 27
MyStr = "1"
Case Else
MyStr = Chr(64 + m)
End Select
MyName = "Query" & m
ConnectString = "URL;http://www.pma-online.org/scripts/showmemlist.cfm?letter=" & MyStr
' Clear any existing
For Each QT In ActiveSheet.QueryTables
QT.Delete
Next QT
' Define a new Web Query
Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range("A1"))
With QT
.Name = MyName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlCellInsertionMode.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlWebSelectionType.xlSpecifiedTables
.WebFormatting = xlWebFormatting.xlWebFormattingAll
.WebTables = "7"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With
' Refresh the Query
QT.Refresh BackgroundQuery:=True
Next m
End Sub
Create QueryTable and populate with ADO Recordset
Returns data from the Northwind database
Sub Add_QueryTable_ADO_Recordset()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim qtData As QueryTable
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Northwind;" & _
"Data Source=IBM"
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
With wsSheet
Set rnStart = .Range("A1")
End With
stSQL = "SELECT * FROM Shippers"
Set cnt = New ADODB.Connection
With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With
'Here we add the Recordset to the created QueryTable.
Set qtData = wsSheet.QueryTables.Add(rst, rnStart)
'In order to view any data the QueryTable need to be refreshed.
qData.Refresh
'Cleaning up.
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
Dim qt As QueryTable
sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
"ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("B1"), Sql:=sqlstring)
.Refresh
End With
CommandText property
Returns or sets the command string for the specified data source. Read/write Variant.
You should use the CommandText property instead of the SQL property, which now exists primarily for compatibility with earlier versions of Microsoft Excel. If you use both properties, the CommandText property's value takes precedence.
For OLE DB sources, the CommandType property describes the value of the CommandText property.
For ODBC sources, the CommandText property functions exactly like the SQL property, and setting the property causes the data to be refreshed.
This example sets the command string for the first query table's ODBC data source. Note that the command string is an SQL statement.
Set qtQtrResults = _
Workbooks(1).Worksheets(1).QueryTables(1)
With qtQtrResults
.CommandType = xlCmdSql
.CommandText = _
"Select ProductID From Products Where ProductID < 10"
.Refresh
End With
CommandType property
Returns or sets one of the XlCmdType constants listed in the following table. The constant that is returned or set describes the value of the CommandText property. The default value is xlCmdSQL. Read/write XlCmdType.
You can set the CommandType property only if the value of the QueryType property for the query table or PivotTable cache is xlOLEDBQuery.
If the value of the CommandType property is xlCmdCube, you cannot change this value if there is a PivotTable report associated with the query table.
Connection property
Returns or sets a string that contains one of the following: OLE DB settings that enable Microsoft Excel to connect to an OLE DB data source; ODBC settings that enable Microsoft Excel to connect to an ODBC data source; a URL that enables Microsoft Excel to connect to a Web data source; the path to and file name of a text file, or the path to and file name of a file that specifies a database or Web query. Read/write Variant.
Setting the Connection property doesn't immediately initiate the connection to the data source. You must use the Refresh method to make the connection and retrieve the data.
When using an offline cube file, set the UseLocalConnection property to True and use the LocalConnection property instead of the Connection property.
Alternatively, you may choose to access a data source directly by using the Microsoft ActiveX Data Objects (ADO) library instead.
This example supplies new ODBC connection information for the first query table on the first worksheet.
Worksheets(1).QueryTables(1).Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"
Workbooks.OpenText
Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.
expression.OpenText(FileName, _
Origin:=xlPlatform.xlWindows, _
StartRow, _
DataType:=xlTextParsingType.xlFixedWidth, _
TextQualifier:=xlTextQualifier., _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar, _
FieldInfo:=xlColumnDataType., _, _
TextVisualLayout, _
DecimalSeparator, _
ThousandsSeparator, _
TrailingMinusNumbers, _
Local)
FileName - Required String. Specifies the file name of the text file to be opened and parsed.
Origin - Optional Variant. Specifies the origin of the text file. Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. Additionally, this could be an integer representing the code page number of the desired code page. For example, "1256" would specify that the encoding of the source text file is Arabic (Windows). If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.
StartRow - Optional Variant. The row number at which to start parsing text. The default value is 1.
DataType - Optional Variant. Specifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.
TextQualifier - Optional XlTextQualifier. Specifies the text qualifier.
ConsecutiveDelimiter - Optional Variant. True to have consecutive delimiters considered one delimiter. The default is False.
Tab - Optional Variant. True to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False.
Semicolon - Optional Variant. True to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False.
Comma - Optional Variant. True to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False.
Space - Optional Variant. True to have the space character be the delimiter (DataType must be xlDelimited). The default value is False.
Other - Optional Variant. True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False.
OtherChar - Optional Variant (required if Other is True). Specifies the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.
FieldInfo Optional xlColumnDataType. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.
You can use xlEMDFormat only if you have installed and selected Taiwanese language support. The xlEMDFormat constant specifies that Taiwanese era dates are being used.
The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting.
If you specify that a column is to be skipped, you must explicitly state the type for all remaining columns or the data will not parse correctly.
If there is a recognizible date in the data, the cell will be formatted as a date in the worksheet even if the setting for the column is General. Additionally, if you specify one of the above date formats for a column and the data does not contain a recognized date, then the cell format in the worksheet will be General.
This example causes the third column to be parsed as MDY (for example, 01/10/1970), the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.
Array(Array(3, 3), Array(1, 2))
If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 0 and 9, as listed in the preceding table.
TextVisualLayout - Optional Variant. The visual layout of the text.
DecimalSeparator - Optional Variant. The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.
ThousandsSeparator - Optional Variant. The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.
TrailingMinusNumbers - Optional Variant.
Local - Optional Variant.
Example
This example opens the file Data.txt and uses tab delimiters to parse the text file into a worksheet.
Workbooks.OpenText filename:="DATA.TXT", _
dataType:=xlDelimited, tab:=True
This example specifies a text file.
Worksheets(1).QueryTables(1).Connection := "TEXT;C:\My Documents\19980331.txt"
TextFileColumnDataTypes property
Returns or sets an ordered array of constants that specify the data types applied to the corresponding columns in the text file that you're importing into a query table. The default constant for each column is xlGeneral. Read/write Variant.
You can use the xlColumnDataType constants listed in the following table to specify the column data types used or the actions taken during the data import.
Use this property only when your query table is based on data from a text file (with the QueryType property set to xlTextImport).
If you specify more elements in the array that there are columns, those values are ignored.
You can use xlEMDFormat only if Taiwanese language support is installed and selected. The xlEMDFormat constant specifies that Taiwanese era dates are being used.
OLEObjects
The OLEObjects collection is a collections of all the ActiveX controls or linked or embedded OLE objects
Add Method
Use the Add method to create a new OLE object
ActiveSheet.OLEObjects.Add
ClassType,
Filename,
Link,
DisplayAsIcon,
IconFileName,
IconIndex,
IconLabel,
Left,
Top,
Width,
Height)
ClassType - A string that contains the programmatic identifier for the object to be created (you must specify either ClassType or Filename but not both)
Filename - The file for the object that you want to create (you must specify either ClassType or Filename but not both)
link - True or false indicating whether to link the object
DisplayAsIcon - True or false indicating whether to display the object as an icon
IconFileName - The file containing the icon to be displayed
IconIndex - The index of the icon within the IconFileName
IconLabel - A text caption to display under the icon
Left - The position (in points) of the upper left corner relative to the upper left corner of the document, default is 0.
Top - The position (in points) of the upper left corner relative to the upper left corner of the document, default is 0.
Width - The width of the object (in points)
Height - The height of the object (in points)
ActiveSheet.OLEObjects.Add(Filename:= _
"C:\Users\proctorr\Desktop\807b93399950447eade5fca69ba898d8.docx", Link:= _
False, DisplayAsIcon:=False).Select
Worksheets(1).OLEObjects.Add FileName:="arcade.gif"
Worksheets(1).OLEObjects.Add ClassType:="Forms.ListBox.1"
Worksheets("Sheet1").OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=300, Top:=63, Width:=50.5, Height:=50)
An ActiveX control on a worksheet has two names:
the name of the shape that contains the control, which you can see in the Name box when you view the sheet,
the code name for the control, which you can see in the cell to the right of (Name) in the Properties window.
When you add a control to a worksheet, the shape name and code name match.
However, if you change either the shape name or code name, the other is not automatically changed to match.
You use the code name of a control in the names of its event procedures.
However, when you return a control from the Shapes or OLEObjects collection for a sheet, you must use the shape name, not the code name, to refer to the control by name.
For example, assume that you add a check box to a sheet and that both the default shape name and the default code name are CheckBox1.
If you then change the control code name by typing chkFinished next to (Name) in the Properties window, you must use chkFinished in event procedures names, but you still have to use CheckBox1 to return the control from the Shapes or OLEObject collection, as shown in the following example.
ActiveSheet.OLEObjects("CheckBox1").Object.Value = 1
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrev