This function returns the day by adding the number of hours to the given date column. This function returns the day by adding the time to the given date column. This function will add the specified number of year(num_of_year) to the given date value. This function will add the specified number of quarter(num_of_quarter) to the given date value. This function will add the specified number of months(num_of_months) to the given date value.
This function will add the specified number of weeks(num_of_weeks) to the given date value. This function will add the specified number of days(num_of_days) to the given date value. This function will return week and year from a given date value with the format (Week, yyyy). This function will return Quarter and year from a given date value with the format (Quarter, yyyy). Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.This function will return month and year from a given date value with the format (Month, yyyy).Ībsolute Quarter - absquarter(date_column) With (Connection:=connstring, _ĭestination:=Range("B1"), Sql:=sqlstring) "ODBC DSN=96SalesData UID=Rep21 PWD=NUyHwYQI Database=96Sales" sqlstring = "select 96Sales.totals from 96Sales where profit < 5" This example creates a new query table on the active worksheet. Set qtQtrResults = ( _Ĭonnection := "TEXT C:\My Documents\19980331.txt",Īrray(xlTextFormat, xlSkipColumn, xlGeneralFormat) Set shFirstQtr = Workbooks(1).Worksheets(1) The remainder of the text file is imported into the third column and has the General format applied to it. The second column is four characters wide and is skipped. The first column in the text file is five characters wide and is imported as text. This example imports a fixed width text file into a new query table. Source:="Select Name, Quantity, Price From Products", _ The example preserves the existing column sorting and filtering settings and layout information for backward compatibility. This example creates a query table based on an ADO recordset. RemarksĪ query created by this method isn't run until the Refresh method is called. You cannot use this argument when a QueryTable object, text file, or ADO or DAO Recordset object is specified as the data source.Ī QueryTable object that represents the new query table. This argument is optional when you are using an ODBC data source (if you don't specify it here, you should set it by using the Sql property of the query table before the table is refreshed). The SQL query string to be run on the ODBC data source. The destination range must be on the worksheet that contains the QueryTables object specified by expression. The cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). A string in the form TEXT where TEXT is required but not localized. The file is read when the Add method is run subsequent calls to the Connection property of the query table will return strings beginning with ODBC or URL as appropriate. The rest of the string is the path and file name of a Data Finder file (*.dqy or *.iqy). A string in the form FINDER where FINDER is required but not localized. A string in the form URL where URL is required but not localized and the rest of the string is used for the URL of the web query. The resulting query table cannot be edited. Microsoft Excel retains the recordset until the query table is deleted or the connection is changed. Data is read from the ADO or DAO recordset. Specifying a QueryTable object causes the Sql argument to be ignored. A QueryTable object from which the query information is initially copied, including the connection string and the SQL text, but not including the Destination range.The ODBC connection string has the form ODBC. A string containing an OLE DB or ODBC connection string.Add ( Connection, Destination, Sql)Įxpression A variable that represents a QueryTables object.