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",""))