Document Not Saved Error on ExportAsFixedFormat

Posted on July 14, 2015 by Dick Kusleika

Recently, I was accusing ExportAsFixedFormat of causing a problem with some code. It turned out to not be the culprit. But I was suspicious because I very often get a Document Not Saved runtime error when I export to PDF in a loop. When I get that error, I can click Debug and F5, and it happily continues until it errors again. Of course, watching something loop eighty-six times to make sure it doesn’t error kind of defeats the purpose of the loop. Today, I took a stand.

I have this code that changes the page fields on a pivot table and exports a range. The result is eighty-six PDFs in a folder that I sew together into one big report. There are two page fields, so there are two loops. Here’s the inner loop.

For Each rCell In .Range(.Cells(vaRows(i), 2), .Cells(vaRows(i), 2).End(xlDown)).Cells
    pt.PivotFields("Final").CurrentPage = rCell.Value
                    
    wshDashFuelCust.Range("AK7").Resize(62, 4).ExportAsFixedFormat xlTypePDF, sPath & rCell.Value & "_" & aProducts(i)
    DoEvents
Next rCell

For Each rCell In .Range(.Cells(vaRows(i), 2), .Cells(vaRows(i), 2).End(xlDown)).Cells

pt.PivotFields("Final").CurrentPage = rCell.Value

wshDashFuelCust.Range("AK7").Resize(62, 4).ExportAsFixedFormat xlTypePDF, sPath & rCell.Value & "_" & aProducts(i)

DoEvents

Next rCell

You see my pathetic DoEvents attempt at avoiding the error. The error stops on the ExportAsFixedFormat line, so the DoEvents doesn’t actually help. When the error happens, a .tmp file is left in the directory. And when I do the Debug, F5 thing, the .tmp file stays there forever. Clearly this is a temporary file that would someday become a PDF if not for this error.

The .tmp file is my evidence that the processed finished. I can ignore the error and as long as there is no temp file, I’ll know the error never occurred. I rewrote the loop thusly:

For Each rCell In .Range(.Cells(vaRows(i), 2), .Cells(vaRows(i), 2).End(xlDown)).Cells
    pt.PivotFields("Final").CurrentPage = rCell.Value
                    
    Do
        On Error Resume Next
            Kill sPath & Dir(sPath & "*.tmp")
            wshDashFuelCust.Range("AK7").Resize(62, 4).ExportAsFixedFormat xlTypePDF, sPath & rCell.Value & "_" & aProducts(i)
        On Error GoTo 0
    Loop While Len(Dir(sPath & "*.tmp")) > 0
    
    DoEvents
Next rCell

For Each rCell In .Range(.Cells(vaRows(i), 2), .Cells(vaRows(i), 2).End(xlDown)).Cells

pt.PivotFields("Final").CurrentPage = rCell.Value

On Error Resume Next

Kill sPath & Dir(sPath & "*.tmp")

wshDashFuelCust.Range("AK7").Resize(62, 4).ExportAsFixedFormat xlTypePDF, sPath & rCell.Value & "_" & aProducts(i)

On Error GoTo 0

Loop While Len(Dir(sPath & "*.tmp")) > 0

DoEvents

Next rCell

As long as there’s a file with a tmp extension, I keep trying to export. The Kill statement needs to be inside the On Error because there won’t be anything to Kill the first time.

After a rigorous test of one time, it worked. I should have put a little loop counter in there to see how many times it errored. And maybe to exit out in case it gets into an infinite loop situation.

Converting SUMs to SUBTOTALs

Posted on July 13, 2015 by Dick Kusleika

Everyone knows that SUBTOTAL ignores filtered rows. Readers of DDoE know that SUBTOTAL also ignores other SUBTOTAL formulas. I tell everyone who will listen about the benefits of SUBTOTAL. It’s one of the best received tips in the ‘Tips and Tricks’ portion of the training I do. But I still get spreadsheets that use SUM and individual adding of cells. When I do, I convert them to SUBTOTAL to make sure there are no errors. Today, I decided to automate that process.

I’ve filled column B over to the right into column C so I can preserve the original data.

With Excel’s color coding and this simple worksheet, you may have spotted the error in the grand total formula. Below is the code I wrote to correct this situation without having to put in all the SUBTOTALs manually.

Public Sub ConvertSumToSubtotal()
    
    Dim rCell As Range
    Dim rStart As Range
    
    Const sSUM As String = "=SUM("
    
    'Only work on ranges
    If TypeName(Selection) = "Range" Then
        'Only work on single columns
        If Selection.Columns.Count = 1 Then
            'rStart will adjust to be where ever the SUBTOTAL range will start
            Set rStart = Selection.Cells(1)
            'loop through the cells and replace SUM with SUBTOTAL
            'change rStart to point to cell just below the SUBTOTAL
            For Each rCell In Selection.Cells
                If rCell.HasFormula And Left(rCell.Formula, 5) = sSUM Then
                    rCell.Formula = "=SUBTOTAL(9," & rStart.Address(0, 0) & ":" & rCell.Offset(-1, 0).Address(0, 0) & ")"
                    Set rStart = rCell.Offset(1, 0)
                End If
            Next rCell
        End If
    End If
    
    'Make the last cell a SUBTOTAL of the whole range
    Selection.Cells(Selection.Rows.Count).Formula = "=SUBTOTAL(9," & Selection.Resize(Selection.Rows.Count - 1, 1).Address(0, 0) & ")"
    
End Sub

Public Sub ConvertSumToSubtotal()

Dim rCell As Range

Dim rStart As Range

Const sSUM As String = "=SUM("

'Only work on ranges

If TypeName(Selection) = "Range" Then

'Only work on single columns

If Selection.Columns.Count = 1 Then

'rStart will adjust to be where ever the SUBTOTAL range will start

Set rStart = Selection.Cells(1)

'loop through the cells and replace SUM with SUBTOTAL

'change rStart to point to cell just below the SUBTOTAL

For Each rCell In Selection.Cells

If rCell.HasFormula And Left(rCell.Formula, 5) = sSUM Then

rCell.Formula = "=SUBTOTAL(9," & rStart.Address(0, 0) & ":" & rCell.Offset(-1, 0).Address(0, 0) & ")"

Set rStart = rCell.Offset(1, 0)

End If

Next rCell

End If

'Make the last cell a SUBTOTAL of the whole range

Selection.Cells(Selection.Rows.Count).Formula = "=SUBTOTAL(9," & Selection.Resize(Selection.Rows.Count - 1, 1).Address(0, 0) & ")"

End Sub

This won’t work in every situation, but this layout is the one I see the most. This layout being SUMs for the subtotals and a big =A1+A2+A3 style formula for the grand total.

Once again SUBTOTAL saves the day and fixes the error. The most common error I see with this layout is in the grand total, but not always. Sometimes the subtotals don’t cover the correct range. It would seem easier when replacing the SUMs to use the same range the SUM uses, but I wanted to make sure I fixed any of those errors too. To do that, I SUBTOTAL from the cell below the previous SUBTOTAL to the cell above the current one.

Pro tip: Use

Ctrl+`

Ctrl+`

to toggle between viewing formulas and values (that’s an accent grave, left of the 1 key on US keyboards).

PowerPivot Stats

Posted on July 1, 2015 by Dick Kusleika

Here’s what a recent PowerPivot process looked like

14,512,074 Rows
7 Columns
14:09 to load data from SQL Server at 30M
5:36 to save file
129MB file size
0:14 recalc time
1,559,700KB used by the EXCEL.EXE process

On this client machine:

Windows 8.1 Pro 64bit
Excel 2010 64bit
36GB RAM
2.4Ghz Xeon processor

Leave your story in the comments.

Excel Is Waiting for another Application to Complete an OLE Action

Posted on June 25, 2015 by Dick Kusleika

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

1	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

1	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

1	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.

AutoHotKey Update

Posted on June 19, 2015June 18, 2015 by Dick Kusleika

Have you been wondering what’s up my keyboarding sleeve lately? Too bad, I’m telling you anyway.

Insert Date

You know how pressing Ctrl+; inserts the current date in Excel? Now I can do that in any program. It’s particularly useful when I’m updating an item at goodtodo.com where I date stamp notes.

#IfWinNotActive ahk_class XLMAIN

^;::
	FormatTime, CurrentDateTime,, M/d/yyyy
	SendInput %CurrentDateTime%
	Return

#IfWinNotActive ahk_class XLMAIN

^;::

FormatTime, CurrentDateTime,, M/d/yyyy

SendInput %CurrentDateTime%

Return

I’ve already re-purposed Ctrl+semicolon in Excel, so it’s in the section that only works outside of Excel.

Stop Helping Me

The other day I accidentally pressed F1. In Excel. For the last time. I could have just disabled F1, but I thought I’d try something different. This figures out what’s in the active cell and searches for it in Firefox.

#IfWinActive ahk_class XLMAIN
f1::
	oExcel := ComObjActive("Excel.Application")
	value := oExcel.ActiveCell.Formula
	Send #2
	Sleep 50
	Send ^k
	Send %value%
	Send {Enter}
	Return

#IfWinActive ahk_class XLMAIN

f1::

oExcel := ComObjActive("Excel.Application")

value := oExcel.ActiveCell.Formula

Send #2

Sleep 50

Send ^k

Send %value%

Send {Enter}

Return

Now when I press F1, I no longer get a separate window, stealing the focus, and not being very helpful. Instead I get a Google search that’s slightly more helpful.

I had to upgrade my AHK to use the ComObjActive function. The

Send #2

Send #2

is because Firefox is pinned to my Windows 7 taskbar in the second location (# is the Windows key in AHK).

Send ^k

Send ^k

(control+k) moves the focus to the Firefox search textbox. If you are using Chrome, for instance, you’d want Ctrl+d because the address bar and the search bar are the same.

That’s not as nice as if I parsed the function, but it gets the job done. I tried to use the clipboard so that I could edit a cell, select a function, and press F1 to get a Google search for this function. I couldn’t get the clipboard to work reliably, no doubt because I was in edit mode. Hmmm, maybe Ctrl+C, then esc to get out of edit mode? Dangerous.

Seriously, Stop Helping Me

Don’t forget VBA help.

#IfWinActive ahk_class wndclass_desked_gsk

    f1::
	Send ^c
	StringLen, length, clipboard
	IfLessOrEqual %length%, 1
		{
		Send ^{LEFT}^+{RIGHT}
		Send ^c
		}
	Send #2
	Sleep 50
	Send ^k
	Send excel vba %clipboard%
	Send {Enter}
	Return

#IfWinActive ahk_class wndclass_desked_gsk

f1::

Send ^c

StringLen, length, clipboard

IfLessOrEqual %length%, 1

{

Send ^{LEFT}^+{RIGHT}

Send ^c

}

Send #2

Sleep 50

Send ^k

Send excel vba %clipboard%

Send {Enter}

Return

This copies the selection. If what it copies is more than a single character, it searches for that. If it’s one character or less, it selects the current word under the cursor and searches for that. In both cases, it puts “excel vba” before the search term. If you want to change that to

Send %clipboard% site:dailydoseofexcel.com

1	Send %clipboard% site:dailydoseofexcel.com

, you won’t hear me complain.

Some VBE Sugar

::ThisPath::ThisWorkbook.Path & Application.PathSeparator

1	::ThisPath::ThisWorkbook.Path & Application.PathSeparator

Type ThisPath and it expands to

ThisWorkbook.Path & Application.PathSeparator

1	ThisWorkbook.Path & Application.PathSeparator

::ppg::Public Property Get

1	::ppg::Public Property Get

Type ppg and it expands to

Public Property Get

1	Public Property Get

And Some Problems

If I type acd in a Windows Explorer address bar (or File Open or File Save dialog) it expands to the Accounting\Restricted\ folder on our main network share. That shortcut used to be acr until I figured out how common acr is in English words. The combination acd appears almost never, at least in my admittedly low brow circles. It does exist in Access’ DoCmd.OpenForm when you want to open the form as a dialog box.

The other major problem I have is writing comments in the VBE. Thankfully I don’t write a bunch of comments, but I do use the words With, If, and For a lot as I’m sure we all do. When I type them, they expand as if I were typing VBA statements. I tried to code something in AHK that would recognize a single apostrophe and ignore stuff that’s typed after it, but I never got it to work. It’s not simply that it didn’t work, it broke just about everything else in that AHK file. I was clearly over my head and deleted the whole attempt. I wish I had saved it so I could at least shown you what not to do.

Filter a Custom Class Using CallByName

Posted on June 18, 2015 by Dick Kusleika

Andrzej asks

Is there a way to dynamically state by which property do I want to filter/sort/unique? ppl.FilterBy(City, “Tokio”).FilterBy(LastName,”Smith”).Unique(FirstName)
Without doing select case

James Brown corrected my response by saying:

You should be able to write a generic filter function in your custom collection class based around CallByName from the object class.

Of course he’s right. Here’s how that might look. Suppose I have some sample contact data.

I could filter by any one of those properties like this

Public Property Get Filter(ByVal sProperty As String, vValue As Variant) As CContacts
    
    Dim clsReturn As CContacts
    Dim clsContact As CContact
    
    Set clsReturn = New CContacts
    
    For Each clsContact In Me
        If CallByName(clsContact, sProperty, VbGet) = vValue Then
            clsReturn.Add clsContact
        End If
    Next clsContact
    
    Set Filter = clsReturn
    
End Property

Public Property Get Filter(ByVal sProperty As String, vValue As Variant) As CContacts

Dim clsReturn As CContacts

Dim clsContact As CContact

Set clsReturn = New CContacts

For Each clsContact In Me

If CallByName(clsContact, sProperty, VbGet) = vValue Then

clsReturn.Add clsContact

End If

Next clsContact

Set Filter = clsReturn

End Property

The comparison value needs to be a variant to account for all the different data types your properties could be. If you had a property of your class that was another class it could complicate things. But this saves me having to write a bunch of Filter properties. So thanks James for making that comment.

Public Sub TestCallByName()
    
    Dim clsContacts As CContacts
    Dim clsFiltered As CContacts
    
    Set clsContacts = New CContacts
    clsContacts.FillFromRange Sheet1.ListObjects(1).DataBodyRange
    
    Set clsFiltered = clsContacts.Filter("State", "Nebraska")
    Debug.Print "Nebraska: " & clsFiltered.Count
    
    Set clsFiltered = clsContacts.Filter("Active", True)
    Debug.Print "Active: " & clsFiltered.Count
    
    Set clsFiltered = clsContacts.Filter("LastPayDate", #4/10/2015#)
    Debug.Print "April 10: " & clsFiltered.Count
    
End Sub

Public Sub TestCallByName()

Dim clsContacts As CContacts

Dim clsFiltered As CContacts

Set clsContacts = New CContacts

clsContacts.FillFromRange Sheet1.ListObjects(1).DataBodyRange

Set clsFiltered = clsContacts.Filter("State", "Nebraska")

Debug.Print "Nebraska: " & clsFiltered.Count

Set clsFiltered = clsContacts.Filter("Active", True)

Debug.Print "Active: " & clsFiltered.Count

Set clsFiltered = clsContacts.Filter("LastPayDate", #4/10/2015#)

Debug.Print "April 10: " & clsFiltered.Count

End Sub

Office 2016 Preview

Posted on May 4, 2015 by Dick Kusleika

From the Office blog: Office 2016 Public Preview Now Available

Intuitive data connecting and shaping capabilities. With integrated Power Query, use Excel as your personal analysis workspace by connecting to and viewing all the data around you. Take advantage of a broad range of data sources, including tables from websites, corporate data like SAP Business Objects, unstructured sources like Hadoop, and services like Salesforce. After bringing all your data together in one place, quickly shape and combine to fit your unique business needs and get to analysis in seconds

Go here for instructions on how to get the preview.

I use Oracle Virtualbox to install previews. So far, the 2016 preview has been remarkably stable, but I’m not one for taking chances.

Emailing Access Report in Email Body

Posted on April 24, 2015 by Dick Kusleika

I’ve been doing a fair bit of work in Access lately, which is why I’m so irritable. I like Access for it’s rapid development characteristics, but I have specific user interface requirements that don’t happen in Access out of the box. By the time I implement those quirks, I feel like I should have just written the damn thing from scratch in C#.

The quirk du jure is sending an Access report via email not as an attachment, but rather in the body of the email. I thought this would be pretty darn easy. I would display the report and the user would use some built-in send function under the File menu to send it off. Oddly, to send an Access object via email, you have to look on the External Data tab, not the File menu. The Export Email function does not have the option of putting the report in the body of the email, only as an attachment. At least as far as I can see. Off to VBA, I guess.

Private Sub cmdEmailBlended_Click()
    
    Dim sFile As String, lFile As Long, sHtml As String
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
        
    UpdateBlendedQuery
    
    'Output the report to HTML in the temp directory
    sFile = Environ$("TEMP") & "\Blended" & Format(Date, "yyyymmdd") & ".html"
    DoCmd.OutputTo acOutputReport, "BlendedRackReport", acFormatHTML, sFile
    
    'Read in the HTML File
    lFile = FreeFile
    
    Open sFile For Input As lFile
        sHtml = Input$(LOF(lFile) - 1, lFile)
    Close lFile
    
    'Put the file contents in the email body
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = "nobody@example.com"
    olMail.Subject = "Special Pricing"
    olMail.HTMLBody = sHtml
    olMail.Display
    
End Sub

Private Sub cmdEmailBlended_Click()

Dim sFile As String, lFile As Long, sHtml As String

Dim olApp As Outlook.Application

Dim olMail As Outlook.MailItem

UpdateBlendedQuery

'Output the report to HTML in the temp directory

sFile = Environ$("TEMP") & "\Blended" & Format(Date, "yyyymmdd") & ".html"

DoCmd.OutputTo acOutputReport, "BlendedRackReport", acFormatHTML, sFile

'Read in the HTML File

lFile = FreeFile

Open sFile For Input As lFile

sHtml = Input$(LOF(lFile) - 1, lFile)

Close lFile

'Put the file contents in the email body

Set olApp = New Outlook.Application

Set olMail = olApp.CreateItem(olMailItem)

olMail.To = "nobody@example.com"

olMail.Subject = "Special Pricing"

olMail.HTMLBody = sHtml

olMail.Display

End Sub

Ah, the ubiquitous DoCmd object. I hate DoCmd (I told you I was irritable). If I was building the Access Object Model, that line would read

ActiveReport.Export sFile, acFormatHTML

1	ActiveReport.Export sFile, acFormatHTML

In Excel, I like to write my own HTML converters, but not so in Access. Taking an Excel Range and turning into an HTML table is one thing. But taking a Recordset and creating an HTML report out of it is nuts. There’s calculated fields, headers and footers, and a whole bunch of other considerations. So I have no problem using the built-in HTML converter, even though my 18 record report produces 199 lines of HTML (actually less than I expected).

On the first go ’round, I encountered a

Input past end of file

1	Input past end of file

error. You might notice on my Input$ statement that I take the length of the file minus 1. That’s not how I normally do it. In fact, I have an AutoHotKey for when I type Input

:*:Input$(::
	sendinput Input$(lof(lfile),lfile)
	Return

:*:Input$(::

sendinput Input$(lof(lfile),lfile)

Return

I couldn’t figure out why I was getting this error. I’m using LOF to get the length of the file, what more could you possibly want. I subtracted one just to see what would happen and it worked. Then I opened the file in Notepad++ to see if I could see what was happening.