Automate Excel

You can turn off automatic calculation with a macro by setting it to xlmanual. Use the following piece of VBA code:

Application.Calculation = xlManual

To turn on automatic calculation from VBA:

Application.Calculation = xlAutomatic

When I need to For..Next..Loop through an entire column I usually use ActiveSheet.UsedRange.Rows.Count to find where to stop. I’ve always had good luck with this approach.

I am also aware that occasionally Excel thinks the last row exists somewhere, but the row is actually empty. I’ve seen this a few times after importing data. From BeyondTechnology:

The Worksheet object’s UsedRange does not always work because the used range (or “dirty area”) of a spreadsheet may be larger than the area actually populated with your records.

I’m bookmarking the article Identifying the Real Last Cell as an alternative to UsedRange for looping through Columns, Rows, and Ranges.

If you would like to hide a macro from appearing as an option in the Macro dialog box, it can be done by declaring it as Private.

The following are two macros, only the first one will appear to the user, the second is declared as Private so it will not be visible.

Sub Macro1()
     Call Macro2
End Sub

Private Sub Macro2()
     MsgBox "You can only see Macro1"
End Sub

Note: This only works if both Macros are in the same Module.

Classic videos from Steve Ballmer, the CEO of Microsoft, the company that makes Excel(I’ll find an Excel spin for all posting digressions).

“Developers”, “Monkey Boy”, and a Remix of the Two

Ballmer sells windows 1.0

How do you export Excel data to be viewed from a cell phone or mobile device?

The following is a basic example of using a macro to export one column from an Excel spreadsheet to a Wml(Wireless Markup Language) file, which can be uploaded to your server and accessed from a cell phone.

The experiment: I have a list in column A of Sheet1 in Excel with the track listing from the CD: Perfect Circle - Mer De Noms . First I want to Export the data from Excel in Wml format, second, upload to my server, and finally view the tracklist from my cell phone’s browser. A graphical representation:

To accomplish this I used a VBA macro and Wml. For a basic understanding of Wml I refer you to another tutorial from w3schools.

You can view the final result of my Excel to Wap experiment at the following address:

www.automateexcel.com/pl/wap1.wml

and can do so in your mobile device’s browser, copy and paste the address into a wap emulator, or by viewing this very blurry picture (time for a new digital camera!).

Try it Yourself
1. Copy the following code to a Module in Excel (help?)
2. Place some text in Column A of Sheet1 and Save your Workbook
3. Run the Macro
4. A Wml file will be created in the same folder as the workbook you have open.
5. Upload the Wml file to your server and view in cell phone.

Sub WAP()

Dim WapTitle As String
Dim WapName As String
Dim DocType As String
Dim Counter As Integer

DocType = "<!DOCTYPE wml PUBLIC """ & "-" _
& "//WAPFORUM//DTD WML 1.1//EN"" ""http://www.wapforum.org/" & _
"DTD/wml_1.1.xml""" & ">"

'location to write file
WapName = ThisWorkbook.Path & "/wap1.wml"

'Kill the file if it already exists
If Len(Dir(WapName)) > 0 Then
    Kill WapName
End If

'get wap title
WapTitle = InputBox("Title", "MyInputTitle", _
"Enter The Title of Your Wap Site")

'exit if no wap title
If WapTitle = "Enter The Title of Your Wap Site" _
Or WapTitle = "" Then
    MsgBox "Sorry You Need a Title - Quitting Now"
    Exit Sub
End If

'open workbook and write wml header info
Open ThisWorkbook.Path & "\wap1.wml" For Append As #1
    Print #1, "<?xml version=""1.0""?>"
    Print #1, DocType
    Print #1, "<wml>"
    Print #1, ""
    Print #1, _
    "<card id=""card0"" title=""" & WapTitle & """>"
    Print #1, ""
    Print #1, "<p>"
       'loop through sheet1, write contents
       For Counter = 1 To ActiveSheet.UsedRange.Rows.Count
       If Not IsEmpty(Sheet1.Cells(Counter, 1)) Then
           Print #1, Sheet1.Cells(Counter, 1) & ""
       End If
       Next Counter
    'write wml footer info and close
    Print #1, "</p>"
    Print #1, ""
    Print #1, "</card>"
    Print #1, "</wml>"
Close #1

End Sub

Credit: “Write to text file” code is a derivative of J-walk’s log users.

Conclusion: After my short introduction to Wml it also appears feasible to create multiple pages from data, and create a menu of links for multiple sets of data. I would also like to research an application to present the user with a way to search a large dataset exported from Excel data.

Any opinions or ideas on the topic of exporting Excel to Wml, particularly possible uses, or other thoughts?

I recently posted about a more intuitive way to add spaces to a string in VBA. Jon Peltier of PeltierTech commented on a way to do this in a spreadsheet formula using the REPT() function.

Let’s assume we want to concatenate(or combine) cells A1 and B1 in another cell. We would also like to add 10 spaces between the two.

Usually in this situation I’ve used open quotes with spaces:

=A1&"          "&B1

Thanks to Jon here’s a more accurate way to concatenate cells A1 and B1 with 10 spaces between, using the REPT() function:

=A1&REPT(" ",10)&B1

Andrew Engwirda from Andrew’s Excel Tips also mantains a Japanese Excel blog by the same name. Pretty cool if you can speak Japanese(I can’t) or just want to see what an excel blog looks like in Japanese.

Andrew’s Excel Tips (Japanese Edition)

You can exit or close Excel without asking the user to save by setting display alerts to False and calling Application.Quit. Here’s some simple example code to exit Excel without prompting the user to save:

Sub ExitWithoutPrompt()
     Application.DisplayAlerts = False
     Application.Quit
End Sub

For “Best Independent Tech Blog” that is. From TechWeb:

“Do you read tech blogs? We’re looking for you to tell us which are the best of the best. Right now, it’s your chance to nominate your favorite independent tech blogs.”

Help push Excel blogs to the top of the list and cast your vote here. For a list of some good candidates, where my first vote goes, one more reason, and where I got this info, visit this Daily Dose of Excel post.

To count the total characters (including spaces) in cell A1:

=len(A1)

To count the total characters (not including spaces) in cell A1:

=LEN(SUBSTITUTE(A1," ",""))

To count the total specific characters in Cell A1, for instance the number of times the letter “d” appears in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,"d",""))

Excel newsgroups are one of the best places on the internet to get Excel answers. Currently 2 out of 5 of the top MS newsgroups belong to Excel categories, and the top newsgroup is Excel Programming.

From MSDN/office:

Contextures has a list of the top Excel posters in the Excel newsgroups.

Link to the Office Newsgroups.

MS webcast links and notes:

There are a couple Excel webcasts at the Office Webcasts site, this page is lacking an RSS feed.

You may also want to check the webcast archives for others, however there isn’t any obvious way to search the webcast archives(the search on the page is for the entire site, a search for “excel webcasts” turns up a mixed bag).

“Discover how to get the most out of Microsoft Office System to save time and be more productive. Tune in to these live and on-demand webcasts to learn time-saving tips for optimizing your use and your team

I’m still searching for a blog from MS on Office apps, not Infopath/Onenote/etc… but what most people know as Office, AKA Excel/Access/Etc. Sorta like the IE blog. Does one exist?

In my searching the closest I’ve come across was this list of Office Development Blogs from Channel9. Not much Excel posting going on though.

Can you turn a worksheet into a game of Pacman? It takes a few minutes to load, then just hit enter to begin game play.

Website (also has Space Invaders)

Direct link to Pacman Download

(via Computer Zen)

How does excel recalculate?

From Microsoft - Recalculation in Microsoft Excel 2002

Elsewhere (11 links, look to the top for the menu) - Calculation Secrets

(via ewbi)

Overview of Office 2003 Developer Technologies. Summed up: XML, Smart Tags, Primary interop assemblies, Research task pane. A snore from the “Godfather of Excel”™ on the article.

"I scanned it. It's Greek to me."...J-walk

Also, What’s New in Visual Studio Tools for Office, Version 2005.

I browsed for the article “What’s Old in Visual Studio Tools for Office” to no avail. Apparently they also have a blog. Aside from Excel’s XMl capabilities, I haven’t seen much on the other topics except from Microsoft sites.

Am I overlooking the link to download the Beta Version of Visual Studio Tools for Office?

Random excel nonsense:
Excel bumper stickers and MrExcel.com thong.
Evidently the thongs are selling well.

link: New Microsoft Office Servers on the Way

“When Office 12 debuts in 2006+, an Excel Server, Visio Server and other brand-new servers are expected in the line-up.”

Microsoft Office Online is offering some election templates. For Word you can download candidate bumper sticker templates, and for Excel they have the Electoral College calculator.

“Predict the next winner of the Presidential election! Just click a button to choose a winning party for each state. It takes 270 votes to win.”

A couple uses of a timer in Excel VBA are pausing code and measuring elapsed time. The following will take you to the respective place I’ve posted an example of each use:

1. Pause code execution for a desired amount of time with Application.Wait (simulating a timer)

link: Hide Excel

2. Measure elapsed time with a Timer in VBA

link: Benchmarking Code