Oh, man, I can’t tell how useful that concatenate function is.
One repetitive use I found is to create OR/AND conditions for Access queries. I copy-paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concatfunc to create a string to use in my Access query.
For example, look at this print screen:
The Range A1:A4 houses the string condition I want to use in my Access query to restrict the fruits from my data. In cell B1 I have the formula
=PERSONAL.XLS!concatfunc(A1:A4,CHAR(34) & ” or ” & CHAR(34))
, and the return string from this function is listed in cell B1.
Now, all you have to do is copy and paste this in Access criteria and put a quotation mark at the start and at the end of this string.
I have found one more use of this when I want to store some values in an Array, using the Array function in VBA.
One more print screen:
In this example, I insert a comma (CHAR(44) instead of string OR, and this function returns a string that I can use in VBA to store these values in an array using Array function, after adding a quotation mark, of course, at the start and at the end.
Recently, I had the need of printing the data structures of many dbf files. A google search reveled the DBF.COM file that would output dbf file structures, number of records, file name, date of last update, etc.
Download this file here: files/DBF.COM (Caution: I downloaded this file from the internet, and I do not make any warranty of it being spyware or malicious ware free. Use it on your own risk.)
To use this file go to Start>Run>cmd and in DOS type:
cd C:/
dbf.com > myfilestru.txt
Note: cd is used to change the current directory
Here’s a print screen:
This will create a file myfilestru.txt in the same directory where your dbf files are residing. This text file will look like this:
Now, that we have a text file all the file structures, we need to create separate workbooks for each dbf file. To do that open this text file in Excel, press Alt + F11 to view the VBA window, double-click the sheet name, and paste this code. After that run this code, and you should have separate workbooks for each dbf file.
Sub FormatThisFile()
‘Define some variables
Dim iLastRow As Integer, i As Integer, sWrkbkNm As String, sNewWrkbkNm As String
Dim iPrevLastRow As Integer, iFirstRow As Integer, iNewWrkbkRow As Integer
On Error GoTo FormatThisFile_Error
‘Turn off the screen update to run the macro faster
Application.ScreenUpdating = False
‘Refer to the current workbook and sheet1
With ThisWorkbook.Sheets(1)
‘get the last row number
iLastRow = .Range(“A65536″).End(xlUp).Row
i = 1
Do While i < iLastRow
’search for string “Structure for”
If InStr(1, .Range(“A” & i), “Structure for”) > 0 Then
‘if found then store the file name
sWrkbkNm = Trim(Right(.Range(“A” & i), Len(.Range(“A” & i)) – Application.WorksheetFunction.Find(“:”, .Range(“A” & i))))
sWrkbkNm = Left(sWrkbkNm, Len(sWrkbkNm) – 4)
iFirstRow = i
’search for string “** Total **”
ElseIf InStr(1, .Range(“A” & i), “** Total **”) > 0 Then
iPrevLastRow = i
‘add a workbook
Workbooks.Add
’save it
ActiveWorkbook.SaveAs ThisWorkbook.Path & “\” & sWrkbkNm
sNewWrkbkNm = sWrkbkNm & “.xls”
‘with this new workbook do copy-paste
With Workbooks(sNewWrkbkNm)
ThisWorkbook.Sheets(1).Range(“A” & iFirstRow & “:” & “A” & iPrevLastRow).Copy Destination:= _
.Sheets(1).Range(“A1″)
iNewWrkbkRow = .Sheets(1).Range(“A65536″).End(xlUp).Row
.Sheets(1).Range(“A4:A” & iNewWrkbkRow).TextToColumns Destination:= _
.Sheets(1).Range(“A4″), DataType:=xlDelimited, Space:=True
.Sheets(1).Range(“A5:A” & iNewWrkbkRow – 1).Delete Shift:=xlToLeft
.Sheets(1).Columns(“A:IV”).EntireColumn.AutoFit
.Sheets(1).Columns(“A:IV”).WrapText = False
’save and close this workbook
.Save
.Close
End With
End If
i = i + 1
Loop
MsgBox “Done”
End With
On Error GoTo 0
SmoothExit_FormatThisFile:
Application.ScreenUpdating = True
Exit Sub
FormatThisFile_Error:
MsgBox “Er
ror ” & Err.Number & ” (” & Err.Description & “) in procedure FormatThisFile”
Resume SmoothExit_FormatThisFile
End Sub
Weka, an open source data mining software, uses arff input data format. You can use this code to convert any Access table to arff format. Download the Access database with code: Convert2Arff.mdb
This can very well be designed using forms, but this should get one started. Use Alt + F11 to see the code, then execute procedure ConvertTbl2Arff to convert a table.
Some highlights of this procedure
- Takes care of spaces in Attribute name and data values
- Finds unique values of nominal variables
- Assigns equivalent ARFF datatype
- Replaces missing values with question marks
If you want to export Access tables using ODBC/DSN connections, use the following code. This procedure uses the File DSN and ODBC connection to export Access tables using DAO object TableDef.
Sub ExportTbls()
Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef
On Error GoTo ExportTbls_Error
sTypExprt = “ODBC Database” ‘Export Type
sCnxnStr = “ODBC;DSN=DSNName;UID=userID;PWD=password” ‘Create the connection string
vStTime = Timer
Application.Echo False, “Visual Basic code is executing.”
Set db = CurrentDb()
‘need a reference to Microsoft DAO 3.x library
For Each tbldef In db.TableDefs
‘Don’t export System and temporary tables
If Left(tbldef.Name, 4) “MSys” And Left(tbldef.Name, 4) “~TMP” Then
‘Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
End If
Next tbldef
MsgBox “Done! Time taken=” & Timer – vStTime
On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub
ExportTbls_Error:
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure ExportTblsODST”
Resume SmoothExit_ExportTbls
End Sub
If you want to delete all or some of the tables from your Access database, you can use this DAO approach. You would need a reference to Microsoft DAO 3.x object library. As shown in the example, you can use an array to store the table names, which you want to keep or delete.
Sub DelteTbls()
Dim sTblNm As String
Dim db As Database, tbldef As DAO.TableDef
Dim i As Integer, Arr As Variant
On Error GoTo DelteTbls_Error
‘You can use an array if you want to delete or not delete specific tables
‘Arr = Array(“Table1″,”Table2″,”Table3″)
Set db = CurrentDb() ‘Set the database object
‘Set the warnings off to suppress messages
DoCmd.SetWarnings False
‘For i = 0 To UBound(Arr)
For Each tbldef In db.TableDefs
‘here you can use equal to or not equal to delete or keep specific tables
‘If Left(tbldef.Name, 4) = Arr(i) Then
‘Don’t delete System or temporary tables
If Left(tbldef.Name, 4) “MSys” And Left(tbldef.Name, 1) “~” Then
Debug.Print tbldef.Name
sTblNm = tbldef.Name
‘Delete table
DoCmd.DeleteObject acTable, sTblNm
End If
Next tbldef
‘Next i
MsgBox “Done!”
On Error GoTo 0
SmoothExit_DelteTbls:
Set db = Nothing
DoCmd.SetWarnings True
Exit Sub
DelteTbls_Error:
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure DelteTbls”
Resume SmoothExit_DelteTbls
End Sub
![[CiteULike]](http://nandeshwar.info/wp-content/plugins/bookmarkify/citeulike.png)
![[del.icio.us]](http://nandeshwar.info/wp-content/plugins/bookmarkify/delicious.png)
![[Digg]](http://nandeshwar.info/wp-content/plugins/bookmarkify/digg.png)
![[Facebook]](http://nandeshwar.info/wp-content/plugins/bookmarkify/facebook.png)
![[Furl]](http://nandeshwar.info/wp-content/plugins/bookmarkify/furl.png)
![[Google]](http://nandeshwar.info/wp-content/plugins/bookmarkify/google.png)
![[Reddit]](http://nandeshwar.info/wp-content/plugins/bookmarkify/reddit.png)
![[Slashdot]](http://nandeshwar.info/wp-content/plugins/bookmarkify/slashdot.png)
![[StumbleUpon]](http://nandeshwar.info/wp-content/plugins/bookmarkify/stumbleupon.png)
![[Technorati]](http://nandeshwar.info/wp-content/plugins/bookmarkify/technorati.png)
![[Twitter]](http://nandeshwar.info/wp-content/plugins/bookmarkify/twitter.png)
![[Email]](http://nandeshwar.info/wp-content/plugins/bookmarkify/email.png)
Recent Comments