Code

Old dates, New VBA

20 December 2006 by Ross McLean

Here’s something i did not know about VBA!

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1166424720/0#8

Cool, now i just have to get a job at the Natural History Museum.

Tags: ,
Categories: Code • 1 Comment »

List Box Resizing problems

14 August 2006 by Ross McLean

Ran in to an old friend today, and it took me a while to remember what to do. After adding a list box to your user form, you’ll want to resize it so that it looks nice. At design time you can get all your controls lined up without too much effort. Like this:

DesignTime.JPG

However when the form is run, the list box automatically resizes itself! Like this!

RunTime.JPG

The answer is to change the a default setting, the “IntegralHeight”, and problem solved. The difference is that now your list box will show parts of lines that don’t fill the list box exactly, rather than resizing to accommodate only whole lines.

fixed.JPG

Tags: , ,
Categories: Code • 2 Comments »

A better way with a Make Table Query?

8 August 2006 by Ross McLean

The situation.  

Back end Access DB, front ended with an Excel Form. Add a new record to a table in the DB and run a Make Table Query to sort the whole table in to alphabetical order. Reload the table into a list box.

I need to make a new table because simply sorting the existing table does not results in a alphabetica import into the list box.

The problem is that when I run the SQL for the update query, if the table name is the same as a table already in the DB then DAO errors. In Access you have to click a message box to allow the deletion of the old table.

From VBA I can use DAO with  "TableDefs.Delete" to remove the old table then the solution works fine.  But I don't really like this idea, it is clunky and I don't like deleting table danger!

So what's a better way?
The workhorse code looks like this:

PLAIN TEXT
VBA:
  1. Sub SendNewProject(sPro As String, sDes As String)
  2.  
  3.     Dim i As Integer
  4.     Dim db As Database, rs As Recordset, r As Long
  5.     Dim sSql As String
  6.  
  7.     Set db = OpenDatabase(gsDataBase)
  8.     Set rs = db.OpenRecordset("tblProject", dbOpenTable)
  9.  
  10.     With rs
  11.         .AddNew
  12.         .Fields("Project") = sPro
  13.         .Fields("Description") = sDes
  14.         .Update
  15.     End With
  16.  
  17.     '''close table we will delete shortly
  18.     rs.Close
  19.     Set rs = Nothing
  20.  
  21.     '''run updatequery to update project list
  22.     sSql = " SELECT tblProject.Project INTO tblProjectS" _
  23.            & " FROM tblProject" _
  24.            & " ORDER BY tblProject.Project;"
  25.  
  26.     '''delete old projectsS table
  27.     db.TableDefs.Delete "tblProjectS"
  28.     '''run make new table
  29.     db.Execute sSql
  30.  
  31.     '''close DB
  32.     db.Close
  33.     Set db = Nothing
  34.  
  35.     MsgBox "Project has been added to the projects list" _
  36.            & vbNewLine _
  37.            & vbNewLine _
  38.            , vbOKOnly, gcsMsgboxhead
  39.  
  40. End Sub

Tags: ,
Categories: Code • No Comments »

Securing VBA code in Office

3 August 2006 by Ross McLean

Most of us know that .dll's are the only real option if security is a genuine issue, but I think the following might be a possible (bodge) solutions for those who many not have VB, and need better security for their VBA code. Anyway, I was looking at this site:

Tech Net - Office 2000/Visual Basic Programmer's Guide

Chapter 17 deals with security. My lighting fast brain crawled in to action and I thought I'd try something.

Now according to this info:

In Access, you can save an .mdb or .adp file as a file type that contains only compiled VBA code without the source code.

These files are called .mde file and most of us will have worked with these before, or at least heard of them. I didn't know the code was compiled though. Doing a little bit of goggling it seems that decompiling is available, but that the VBA code remains compiled, i.e. forms and report are easy to decompile, but the VBA remains compiled good news.

See where we are heading? You can call this .mde code from you Excel/Word etc VBA, and your more sensitive VBA code can be kept in a complied- harder to get at - .mde file. In theory it's a bit like putting your code in a proper .dll it's compiled code. Which kinda begs the question, why can you do this with all our VBA?

Here is an example I've put it into a UDF, and it runs in such a way as to be dog slow, but it could be implemented better, or if the situations was different the performance hit would be less noticeable I'm sure you'll get the idea. I've inculed the orginal mdb, and you can do all the other passwording etc to this mde before making it a mde. I've also chnaged the file extention to change the icon, whcih is of little importance.

So, it's Heath Robison, it's far from ideal but it might be useful to someone; oneday! A nice little idea I though"¦Â 

Tags: , , ,
Categories: Code, Downloads, General • 3 Comments »

Selection to CSV

18 June 2006 by Ross McLean

Obviously you could adapter it to do what ever type of text file you like (well, and Excel can handle)

PLAIN TEXT
VBA:
  1. Option Explicit
  2.  
  3. Private Const BIF_RETURNONLYFSDIRS As Long = &H1
  4. Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
  5. Private Const BIF_RETURNFSANCESTORS As Long = &H8
  6. Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
  7. Private Const BIF_BROWSEFORPRINTER As Long = &H2000
  8. Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
  9. Private Const MAX_PATH As Long = 260
  10.  
  11. Type BrowseInfo
  12. hOwner As Long
  13. pidlRoot As Long
  14. pszDisplayName As String
  15. lpszINSTRUCTIONS As String
  16. ulFlags As Long
  17. lpfn As Long
  18. lParam As Long
  19. iImage As Long
  20. End Type
  21.  
  22. Type SHFILEOPSTRUCT
  23. hwnd As Long
  24. wFunc As Long
  25. pFrom As String
  26. pTo As String
  27. fFlags As Integer
  28. fAnyOperationsAborted As Boolean
  29. hNameMappings As Long
  30. lpszProgressTitle As String
  31. End Type
  32.  
  33. Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
  34. ByVal pidl As Long, _
  35. ByVal pszBuffer As String) As Long
  36.  
  37. Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
  38. lpBrowseInfo As BrowseInfo) As Long
  39.  
  40. Public Sub Save_To_Where()
  41.  
  42. Dim sFolderName As String
  43. Dim sFileName As String
  44. Dim sdefult As String
  45.  
  46. sdefult = "M.I.E Export to CSV - " & Application.UserName & " - " _
  47. & Round((Timer), 0)
  48.  
  49. sFolderName = BrowseFolder("Save Text File Where?")
  50. If sFolderName = "" Then
  51. Exit Sub
  52. Else
  53. sFileName = InputBox("Entre the file name you would like to use", "File Name", sdefult)
  54. If Len(sFileName) = 0 Then
  55. Exit Sub
  56. End If
  57. End If
  58.  
  59. SaveAsText sFolderName, sFileName
  60.  
  61. End Sub
  62.  
  63. '''Fuction to get directory
  64. Function BrowseFolder(Optional Caption As String = "") As String
  65.  
  66. Dim BrowseInfo As BrowseInfo
  67. Dim FolderName As String
  68. Dim ID As Long
  69. Dim Res As Long
  70.  
  71. With BrowseInfo
  72. .hOwner = 0
  73. .pidlRoot = 0
  74. .pszDisplayName = String$(MAX_PATH, vbNullChar)
  75. .lpszINSTRUCTIONS = Caption
  76. .ulFlags = BIF_RETURNONLYFSDIRS
  77. .lpfn = 0
  78. End With
  79.  
  80. FolderName = String$(MAX_PATH, vbNullChar)
  81.  
  82. ID = SHBrowseForFolderA(BrowseInfo)
  83.  
  84. If ID Then
  85. Res = SHGetPathFromIDListA(ID, FolderName)
  86. If Res Then
  87. BrowseFolder = Left$(FolderName, InStr(FolderName, _
  88. vbNullChar) - 1)
  89. End If
  90. End If
  91.  
  92. End Function
  93.  
  94. Public Sub SaveAsText(sFolder As String, sName As String)
  95.  
  96. On Error GoTo ErrorHandler
  97.  
  98. Application.ScreenUpdating = False
  99.  
  100. Selection.Copy
  101.  
  102. Workbooks.Add
  103. ActiveWorkbook.Sheets(1).Paste
  104. ActiveWorkbook.SaveAs Filename:=sFolder & "\" & sName & ".csv", FileFormat:=xlCSVMSDOS
  105. Application.DisplayAlerts = False
  106. ActiveWorkbook.Close
  107. Application.DisplayAlerts = True
  108. Application.ScreenUpdating = True
  109.  
  110. Exit Sub
  111.  
  112. '''Error handerling
  113. ErrorHandler:
  114. If Err.Number = 1003 Then
  115. MsgBox "Error, did you use <,>,?,[,], :, | or *" _
  116. & vbNewLine & "Make sure the folder exists" _
  117. & vbNewLine & "Make sure the ile/path name is not onger than 218 letters" _
  118. & vbNewLine & "Make sure the folder is not read only", _
  119. vbOKOnly, "Error Exporting Text File"
  120. Exit Sub
  121. Else
  122. MsgBox "An unexpected error occured, export aborted", vbOKOnly, "Error Exporting Text File"
  123. Exit Sub
  124. End If
  125.  
  126. End Sub

Tags: , ,
Categories: Code • No Comments »

« Older Entries