UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA2HTML - UA Tweak    
 
   
KingMartin
post Jan 21 2005, 04:22 PM
Post#1


Retired Moderator
Posts: 10,959
Joined: 9-October 03
From: Prague,CZ / Kiev,UA


Recently I was PM'd that the downloads from this thread are no more available.

So, I am posting Juan Pablo González VBA-to-HTML maker here. I have slightly tweaked the add-in so that it works for UA forum.

(1) Install the Add-In (look in Excel Help how)

(2) Switch to VBA editor (Alt+F11)

(3) Select the code, right-click, Convert Selected Code/Procedure

(4) Paste the HTML into your post

(5) Don't forget to enable HTML for your post

Drawbacks:

(1) The code is an excellent compromise between complexity and functionality. Nevertheless, in some cases the colors may not be correct, e.g.¨
Sub FindEnd()
[a65536].End(3)(2) = "Hello"
End Sub


on the other hand, some relatively complex code conversion works flawlessly:
Sub UniqueRecsADO()
Dim rs As ADODB.Recordset
Dim strConn As String, strSQL As String
Dim strShtName As String, strData As String
Dim shNew As Worksheet, fld As Field
Dim c As Long

strShtName = ActiveSheet.Name
strData = ActiveSheet.[A1].CurrentRegion.Address(False, False)

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
strSQL = "SELECT DISTINCT * " & _
"FROM [" & strShtName & "$" & strData & "] "

Set rs = New ADODB.Recordset
rs.Open strSQL, strConn, 2, 3, 1

Set shNew = Sheets.Add
If Not rs.EOF Then
With shNew
c = 1
For Each fld In rs.Fields
.Cells(1, c) = fld.Name
c = c + 1
Next fld
.[A2].CopyFromRecordset rs
End With
Else
MsgBox "No records returned:" & vbCrLf & strSQL
End If
rs.Close
Set rs = Nothing
End Sub


(2) You can't copy the code from the post directly into a module. Paste it into Excel sheet first or into any other document that supports pasting from clipboard, then re-copy and re-paste into your module.

Enjoy.
frown.gif
Martin
Edited by: GlenKruger on Mon Jan 24 13:14:21 EST 2005.
Attached File(s)
Attached File  VBHTMLMakerUA.zip ( 28.98K )Number of downloads: 39
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:17 PM