Category Archives: Automation

Fun with Excel; A Wheel Of Fortune

By in Automation, Downloads, For Normal People, MVP, VBA on .

Hi everyone,

Long time no see!

It seems the busier one is, the more work gets done. I’ve published a nice article today, in which I explain how I created a Wheel Of Fortune made entirely in Excel some years ago. This is what it looks like:

Wheel Of Fortune

Wheel Of Fortune

Enjoy!

Jan Karel Pieterse

www.jkp-ads.com

Excel Is Waiting for another Application to Complete an OLE Action

By in Automation, VBA on .

Have you ever seen this message? It’s not an error. You can’t click Debug and go see which line of code it’s on when this happens. You can’t even click Cancel. All you can do is click OK every 10 seconds or so until it’s done. Brutal.

I ran into this message recently on some code that someone else wrote but that I’d modified (see how I’m already deflecting the blame). The code runs through a hundred or so customers and sends them an email. Each customer has its own worksheet and that worksheet is turned into HTML to be used in the body of the email. Incidentally they used Ron de Bruin’s RangeToHTML function to do the conversion. I happened to have written that function back when I had a website called dicks-clicks.com. Ah, memories.

The code I modified was working well for a few weeks before it started acting up. One line in the code looks like this

sh.ExportAsFixedFormat xlTypePDF, sAttachFile

That saves the sheet as a PDF. I use the ExportAsFixedFormat method a lot in loops and I get the Run-time error 1004. Document not saved. the document may be open, or an error may have been encountered when saving error every so often. When I get this error I hit Debug and F5 and everything works fine. I know it’s a timing issue, but haven’t taken the time to figure out how to avoid it. It started happening on this customer email workbook. I couldn’t hardly ask a normal user to click Debug, F5, and close the VBE when it’s done. I’m not a monster.

I put a one second delay before line to allow Windows to have time to release the file lock or whatever the heck is going on. It only executes on about seven of the 100 customers, so it makes a 10 second procedure run in 17 seconds. We can live with that. And it worked. No more errors.

Everything was fine until the OLE Action message started popping up a couple days later. Of course when anything goes wrong after a code change, you have to blame the code change. The angry villagers were at my door demanding that the one second delay be removed. I wasn’t convinced (spoiler: I’m the hero in this story). I sat at the user’s computer, ran the code, and got the message. I changed the status bar to show me which customer it was on when the message appears. When I ran the code again, it was on Vandelay Industries. We looked at some past Vandelay emails and we noticed that the format was all messed up in yesterday’s email, but otherwise looked OK. Formatting problems don’t cause OLE messages, so I ignored it.

I did some Binging and saw my old buddy Shane Devonshire recommended checking the Ignore other applications that use Dynamic Data Exchange checkbox in Tools – Options. I could tell he was grasping at straws, but I was at the straw-grasping stage, so I went with it. I ran the code for the third time (a tedious process because of all the OLE messages) and it got stuck on Vandelay Industries again. A clue!

I discussed this new information with the user. Since Vandelay did not get a pdf attachment, I concluded that the problem was Outlook and not whatever generates PDFs from Office. Maybe we messed up the email address and Outlook was churning away trying to resolve it. Nope, no change there. But he did mention that he added a note to the bottom of their worksheet. More specifically, he copied the note from another customer’s sheet and pasted it to Vandalay’s. Oh, and one other thing. When he pasted the message, he accidentally selected the entire row, which put the message in every cell in that row, rather than just the first one. But he deleted all the extraneous messages, so it was fine.

“Aha!”, I said. I went to their sheet and pressed Ctrl+End. That took me to cell XFD92. In the code, the (now enormous) UsedRange was being passed to RangeToHTML. I went to the Outbox in Outlook and there was a 43MB message sitting there staring back at me. The OLE Action that Excel was waiting for Outlook to complete was rending 43MBs of HTML in a message.

The quick fix was to delete all the columns in that sheet that I didn’t want, save, close, and reopen. Fixed. As for the code, it’s tempting to use an alternative method for finding the real used range. That solves the email problem, but it doesn’t fix the root of the problem – a messed up UsedRange that’s unnecessarily bloating the file.

In the end, I decided to test the number of columns and rows in the UsedRange, and if they’re over a threshold, raise an error. That will allow the user to fix the root and rerun the procedure.

Building an Excel Add-in

By in Add-ins, Application Object, Automation, Classes, Excel Advanced, MVP, VBA on .

Hi there!

Only recently I read this quote somewhere: “If you want something done, ask a busy person”. I found two entirely different people as the originator of this quote: Benjamin Franklin and Lucille Ball. I wonder which it is…

Well, turns out I’ve been quite busy as of late. So I decided it was time to dust off some old stuff I prepared to add to my site but never came round to finishing (I must have become less busy when I was almost done :-) ).

If you’re about to embark on the journey to create an add-in out of a set of macro’s you have been using for some time now, this article is a nice read as it takes you through most of the steps needed when building an add-in for Excel.

Enjoy!

 

Jan Karel Pieterse

www.jkp-ads.com

 

Avoiding Date Conversion When Pasting an HTML Table

By in Automation, External Data, VBA on .

From Get Data From a Website…, MD comments:

However, the info in the table I copy is formatted ## / ## whereby # represents a number.
So when it tries to paste 10 / 10 in the worksheet for example, it auto-changes to 10/okt in my sheet.

This is a common problem that’s hard to solve. There’s no setting I can find in Excel that tells it to stop converting things that look like dates into dates. Since I’m reading in the data and putting it in the clipboard, I can just message the data before I do it. That’s not so easy when you just want to copy and paste, but if you’re using code, you may find the technique useful.

In the above linked post, I automated Internet Explorer to login to a website. I don’t automate Internet Explorer any more, preferring XML instead. But it doesn’t matter which you use. It all ends up in an HTMLDocument, so it’s the same from there. Also, this example doesn’t log into a webpage. It uses Contextures’ Sample Data.

Sub GetTableNoDateConversion()
   
    Dim xHttp As MSXML2.XMLHTTP
    Dim hDoc As MSHTML.HTMLDocument
    Dim hTable As MSHTML.HTMLTable
    Dim hCell As MSHTML.HTMLTableCell
    Dim doClip As MSForms.DataObject
   
    'Get the webpage
    Set xHttp = New MSXML2.XMLHTTP
    xHttp.Open "GET", "http://www.contextures.com/xlSampleData01.html"
    xHttp.send
   
    'Wait for it to load
    Do: DoEvents: Loop Until xHttp.readyState = 4
   
    'Put it in a document
    Set hDoc = New MSHTML.HTMLDocument
    hDoc.body.innerHTML = xHttp.responseText
   
    'Find the third table
    Set hTable = hDoc.getElementsByTagName("table").Item(2)
   
    'Fix anything that looks like a date
    For Each hCell In hTable.Cells
        If IsDate(hCell.innerText) Then
            hCell.innerText = "'" & hCell.innerText
        End If
    Next hCell
   
    'put it in the clipboard
    Set doClip = New MSForms.DataObject
    doClip.SetText "<html>" & hTable.outerHTML & "</html>"
    doClip.PutInClipboard
   
    'paste it to the sheet
    Sheet1.Select
    Sheet1.Range("A1").Select
    Sheet1.PasteSpecial "Unicode Text"
   
    'Make the leading apostrophes go away
    Sheet1.Range("A1").CurrentRegion.Value = Sheet1.Range("A1").CurrentRegion.Value
   
End Sub

Once I get the table into an HTMLTable object, I loop through all the HTMLTableCells to see if any of them looks like a date. If so, I put an apostrophe in front of it. The apostrophe is the Excel way to say “No matter what I type next, assume it’s text”. Except when you’re pasting special as Unicode Text. In that case, it doesn’t hide the apostrophe like it should. So the last line of the code is the equivalent of pressing F2 and Enter all the say down the column and forcing Excel to reevaluate its decision to ignore that apostrophe.

A VBA performance class

By in Automation, Classes, Excel Advanced, MVP, Performance, VBA, VBA Advanced on .

Hi everyone!

If you write a lot of VBA code you probably sometimes run into performance issues. A customer rings you and complains that the Excel file you built for them does not perform well. I’ve written a small article on my site, inspired by a post here: Error Handling via an Error Class. I needed a quick way to log and report performance of subroutines and I also wanted to see how many times a routine was called.

See: A VBA performance class

Enjoy!

Jan Karel Pieterse
www.jkp-ads.com

Sending Images via WinSCP

By in Arrays, Automation, File Operations on .

Since my recent move to Digital Ocean for hosting, I’ve had to make a change to how I upload images for this blog. I used to create an FTP file and a batch file, but as far as I know that doesn’t support SFTP. I’m using WinSCP to transfer files manually and learned that it has a command line interface. I made a procedure called SendViaSCP to replace my SendViaFTP.

Public Sub SendViaSCP(vFname As Variant)
   
    Dim aScript() As String
    Dim i As Long
   
    ReDim aScript(1 To 4 + UBound(vFname))
   
    aScript(1) = "option batch abort"
    aScript(2) = "option confirm off"
    aScript(3) = "open sftp://username:password@000.000.000.000"
    aScript(UBound(aScript)) = "exit"
   
    For i = LBound(vFname) To UBound(vFname)
        aScript(3 + i) = "put " & Dir(vFname(i)) & " /home/wordpress/public_html/blogpix/"
    Next i
   
    Open "winscpup.txt" For Output As #1
    Print #1, Join(aScript, vbNewLine)
    Close #1
   
    Shell "winscpup.bat"
   
End Sub

The vFname arguments is a variant array that holds all of the files I selected from Application.GetOpenFileName. The aScript array holds three lines of setup, a line for each file, and an exit line.

The commands are joined together and written to a batch file and the batch file is run. It doesn’t solve the problem that Billkamm and Haines solved of having your username and password in a batch file, but I can live with it.

You might be wondering why I don’t just use the file upload functions in WordPress. What fun would that be?

A new tool: Trusted Document Manager

By in Automation, Downloads, Excel 14, Excel 15, Excel 2013, Excel Advanced, MVP, VBA on .

Hi everyone!

I have just published a new tool today, Trusted Document Manager. This little tool enables you to manage your list of trusted documents. Currently, Excel only allows you to either leave the list intact, or delete the entire list. This means all of your currently trusted documents become untrusted again so you have to enable macro’s on all of them once again. The tool allows you to remove just one file, remove an entire folder or even an entire drive. Also it offers to possibility to remove files which no longer exist from the list.

This is what the tool looks like:

ScreenshotOfTrustedDocManager

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Office 2013 VBA help files

By in Automation, Excel 2013, Help, MVP, VBA on .

Hi All,

Microsoft announced today that the Help files for Office 2013 VBA have been made available for download.
The help files are for:

Access
Excel
Office Shared
OneNote
Outlook
PowerPoint
Publisher
Word
Visio

Note that these files do not offer context sensitive help (F1), they are presented as stand-alone help files which you have to open manually.

I invite you all to give your opinions about VBA help. Make sure you include the Office version with your comments.