Opening Dynamic Text file in Excel

Query Table for Text / CSV Files

If you update some Excel frequently, you can keep it as shared and then ask your fellow colleagues to check if often (refresh)

One of the good option is to have them as CSV file and use query table to update it regularly

Sub TXT_QueryTable()

Dim ConnString As String

Dim qt As QueryTable

ConnString = "TEXT;C:\Temp.txt"


Set qt = Worksheets(1).QueryTables.Add(Connection:=ConnString, _
Destination:=Range("B1"))

qt.Refresh



End Sub


The Refresh method causes Microsoft Excel to connect to the query table’s data source, execute the SQL query, and return data to the query table destination range. Until this method is called, the query table doesn’t communicate with the data source.

Query Table with Excel as Data Source

Query tables can be of great help if you need to extract particular data from a data source

It 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

However, it need to be SQL server or a Microsoft Access database always. You can use CSV file or our fellow Microsoft Excel spreadsheet as a data source for QueryTable

Here is one such example, which extracts data from MS Excel sheet


Sub Excel_QueryTable()

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
Destination:=Range("B1"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

Use the Add method to create a new query table and add it to the QueryTables collection.

You can loop through the QueryTables collection and Refresh / Delete Query Tables

If you use the above code for Excel 2010, you need to change the connection string to  the following

ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Om\Documents\SubFile.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"
Else it will thrown an 3706 Provider cannot be found. It may not be properly installed. error

See also:

Opening Comma Separate File (CSV) through ADO

Using Excel as Database using VBA (Excel ADO)

Create Database with ADO / ADO Create Database

ADO connection string for Excel

Combining Text Files using VBA

Visual Basic Application to Merge Text Files

Multiple utilities are available to split & merge text files. However, here is a simple one my friend uses to merge around 30 ascii files into one

It uses File System Object and you need to add a reference of Microsoft Scripting Runtime

Sub Append_Text_Files()

Dim oFS As FileSystemObject
Dim oFS1 As FileSystemObject

Dim oTS As TextStream
Dim oTS1 As TextStream

Dim vTemp

Set oFS = New FileSystemObject
Set oFS1 = New FileSystemObject


For i1 = 1 To 30

Set oTS = oFS.OpenTextFile("c:\Sheet" & i1 & ".txt", ForReading)
vTemp = oTS.ReadAll

Set oTS1 = oFS.OpenTextFile("c:\CombinedTemp.txt", ForAppending, True)
oTS1.Write (vTemp)

Next i1

End Sub

The code is simple.. it searches for files from Sheet1.txt ...Sheet30.txt and copies the content into one variable. Then it appends the content to CombinedTemp.txt

Open XML File in Excel

Here are the primitive commands to open an XML file in Microsoft Excel.

Sub Open_XML_File()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML("c:\sample.xml")

End Sub




Sub Open_XML_File_As_List()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML(Filename:="c:\sample.xml", LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList)

End Sub


This option will work for Excel 2003 and above

Visual Basic - Special Folders (Temp Folder , System Folder)

Here is a simple routine to get special folders like temporary folder etc:

Sub Get_Special_Folders()

' Uses File System Object
' Need to have reference to Microsoft Scripting Runtime

On Error GoTo Show_Err

Dim oFS As FileSystemObject
Dim sSystemFolder As String
Dim sTempFolder As String
Dim sWindowsFolder As String

Set oFS = New FileSystemObject

' System Folder - Windows\System32
sSystemFolder = oFS.GetSpecialFolder(SystemFolder)

' Temporary Folder Path
sTempFolder = oFS.GetSpecialFolder(TemporaryFolder)

' Windows Folder Path
sWindowsFolder = oFS.GetSpecialFolder(WindowsFolder)

Dim a
a = oFS.GetFolder("m:\9.3 BulkLoad\BLT1_Base15.6\Reports\08-Nov-2007\Output\")

If Not oFS Is Nothing Then Set oFS = Nothing

Show_Err:

If Err <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Err.Clear
End If
End Sub

You need to have reference to Microsoft Scripting Runtime to execute the above code

Array Dimensioning in Visual Basic

Dimensioning Arrays in Visual Basic

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).

You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless you’re using the Preserve keyword, in which case, no changes of data type are permitted.

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

Here is an example of Array redimensioning

Sub Array_Dimensioning()

Dim arPreserved() As Integer ' Preserved Array
Dim arErased() As Integer ' Array without Preserve

ReDim Preserve arPreserved(1, 1)
ReDim arErased(1, 1)

arPreserved(1, 1) = 1

ReDim Preserve arPreserved(1, 2)
arPreserved(1, 2) = 2

ReDim Preserve arPreserved(1, 3)
arPreserved(1, 3) = 3

ReDim Preserve arPreserved(2, 3) ' This statement will throw and error

' whereas the following statement will not as the Array is not preserved (Erased)
ReDim arErased(2, 1)

End Sub

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The arPreserved falls under this category. However, arErased you can redimension the array in any dimension, but the contents will be erased with every Redim statement

Comparing two Word Documents using Word VBA

Compare Word Documents using VBA

Here is a simple routine, which will compare two Microsoft Word documents and return the status.


Sub IsDocument_Equal()

Dim oDoc1 As Word.Document
Dim oResDoc As Word.Document

' Delete the tables from both the document

' Delete the images from both the document

' Replace Paragraphs etc

Set oDoc1 = ActiveDocument

' comparing Document 1 with New 1.doc
oDoc1.Compare Name:="C:\New 1.doc", CompareTarget:=wdCompareTargetNew, DetectFormatChanges:=True

'This will be the result document
Set oResDoc = ActiveDocument

If oResDoc.Revisions.Count <> 0 Then
'Some changes are done
MsgBox "There are Changes "
Else
MsgBox "No Changes"
End If

End Sub

Convert URLs to Hyperlinks using VBA

Automatically create Hyperlinks for all URLs in a document

Microsoft Word has in-built intelligence to convert the URLs or Web Addresses to Hyperlinks automatically. This functionality is executed when you type some website/email address in word document.

For some reason, if you want to be done on the Word document at a later stage you can do the following:

Sub Make_URLs_as_HyperLinks()


Options.AutoFormatReplaceHyperlinks = True
ActiveDocument.Select
Selection.Range.AutoFormat
Selection.Collapse

Options.AutoFormatReplaceSymbols
End Sub

Warning: I have set only AutoFormatReplaceHyperlinks = True and not set/reset others. You need to check all options as autocorrect/autoformat can cause undesirable changes that might go unnoticed

Run a Automatic Macro in Word Document

Execute Word Macro on File Open

There are numerous instances where one stores the word document format as a Microsoft Word template. When the user opens the document (using the template), some macro needs to be executed. This can be achieved by RunAutoMacro Method of Word VBA

Sub Run_Macro_In_WordDocument()

Dim oWD As Word.Document
Set oWD = Documents.Add("c:\dcomdemo\sample.dot")
oWD.RunAutoMacro wdAutoOpen

End Sub

Here a new document is open based on the Sample.dot template and once the document is open the AutoOpen macro is fired

RunAutoMacro Method can be used to execute an auto macro that's stored in the specified document. If the specified auto macro doesn't exist, nothing happens

On the other hand, if a normal macro (not auto open etc) needs to be executed, Run method can be used

Application.Run "Normal.FormatBorders"