Well, it’s the weekend again. I’m heading to Lake of the Ozarks, MO for a little boating. I’ll be taking my laptop because I have a lot of work piling up. I’ll be back on Sunday or Monday to talk about refedit controls in userforms.
Feel free to use the comments to discuss issues, ask questions, or just tell me what you are doing this weekend.
I’m going to stack two dozen Ding-Dongs in a pyramid then put my hands behind my back and eat them like I’m in a contest.
Who else wants to party with Mike??
For those who’slooking for something to fill the time with during the weekend:
Ubuntu (Linux for human beings)
http://www.ubuntu.com/
GNumeric:
http://www.gnome.org/projects/gnumeric/index.shtml
Check out the functions included in GNumeric:
http://www.gnome.org/projects/gnumeric/functions.shtml
Kind regards,
Dennis
Dick will be back Sunday or Monday to ask how to revive a laptop that fell in the Lake of the Ozarks.
I am going to try is see if it is possible to write to a closed file using ADO – for distinct Cells in a template…Not sure if it can be done……
The end objective is to mimic the Template wizard with data tracking with ADO for an Invoice template…
May be some MVP knows how to do it already…. In which case I am just going to plead with him/her to give me the code :-)
Wow! A weekend of Ding Dongs and Ho Hos. Should be enough information I think… :-D
I’ve been redecorating my home all summmer, finishing the paintwork this weekend. Too many chemicals to breathe in, too many cold beers to drink, and too little sleep. But it’s going to look great and it has been pretty fun to do.
Some people, like me, have accidents with Refedits all the time and claim them buggy. Others never encounter problems. Hope your post and the following discussion can shed some light on that.
Tushar is the only person I know who claims that RefEdits have never caused problems.
and all those claims came after he returned from that alien space ship. It really makes you think, doesn’t it ? …
Just curious, how is the “Support Classic VB” petition going? Any response from MSFT?
Here’s an interesting experiment for those of you who have a website.
-Create an xlsx or xlsm file in Excel.
-Post it on your website as is (do not zip it)
-Now try to download it.
It will come down as a zip file with all XML parts exposed!
Well…at that’s what happens on my site anyway.
Looks like you have to zip any Office 2007 files you want downloaded in one piece.
For those of you who don’t have a website try to download this xlsm file.
Notice the extension on the file is xlsm. You will get a zip file. To use the file, you have to replace the .zip extension with .xlsm!
http://www.datapigtechnologies.com/downloads/ToggleTabs.xlsm
Let that bake your noodle for a while.
I regrouted my shower tiles. I’ve never done any grouting before so it took awhile. It’s one of those unrewarding jobs – it needed to be done, but it’s not exactly going to improve my quality of life.
I just made a batch of fresh pesto from the backyard basil patch and ate a bunch on some pasta, along with tomatoes and cucumbers from the garden. Mighty fine.
And I’ve been working on my first VB program. I read two chapters from Professional Excel Development on VB and Excel, which were of course helpful.
In VB, I was trying to pass an array to a userform property. It didn’t work until I changed the array to be built using Redim Preserve in a loop. Before doing this, I got the message “can’t copy to an array.” So I’m wondering if there’s some structural difference between an array dimensioned in its declaration and one dimensioned using Redim?
I finished off some notes about my Excel ‘challenge of the week’. It was to do with setting a range variable to only include the visible rows in an autofilter that has criteria applied. A combination of trial and error, Google, bad language, xlspecialcells, offset, resize, intersect, and finally union to isolate just two columns from the visible filtered data. Said two columns then get dumped into a chart.
At which point I encountered a minor issue with the default number of tick-lables on my X axis which didn’t show up when I used a chart-sheet but did when I used a chart object with a lot of data on the X axis. The confusing part was that the identical chart-rendering code was used for both a small amount of data and the larger amount but the resulting chart had different tick-label spacing – and I couldn’t find any reference to this behaviour so coded around it.
Learnt a lot about detecting if autofilter was switched on, if criteria is being applied and a load about ranges (hadn’t used union before). Which makes a for a very good weekend in my book.
Mike, Firefox doesn’t change the extension to ZIP. It must be an Internet Explorer thing.
Also, you can drag that zip file into Excel and it opens just fine. Excel never did care about file extensions.
Mike & John –
That file came down as an xlsm file on my computer. Then when I double clicked on it, Excel 2003 tried to open it, and I thought, great, let’s see what crashes. (I have 2003 and 2007 both installed on this computer, despite the warnings.) But a little window popped up, told me Conversion in Progress, and it opened in 2003. Then I checked the Save As dialog, and see half a dozen Excel 12 file types at the bottom of the Save as type dropdown.
I guess the translators were installed with the rest of the beta.
Doug –
Not like I have much experience, but how did you pass the array? I generally pass arrays like this as Variants, and deal with them at the receiving end. I know it’s not as efficient, but it makes my life a little easier.
I also never use singles or integers, preferring to use doubles and longs. Probably more effort has been made by the real programmers to optimize doubles and longs than singles and integers, and for my programs, the loss of efficiency is probably not noticible. Also, I never have to go back and redeclare everything if I need to count higher than 32k.
Gruff –
If you don’t explicitly set the tick spacing (and alignment) in your code, it will produce different results depending on font size, label length, axis length, chart size, and chart location (and probably on other unidentifiable parameters in Excel’s internal algorithms). Keep an eye on the font autoscaling as well.
HA! Mike’s xlsm file came down as an xlsm file, no problem. Then I clicked on the link to a zip file (the pareto plotter that Deb just posted on Contextures.com), and my Open/Save/Cancel dialog identified it as an Excel 12 macro enabled workbook. When I clicked on Open, Excel 12 launched (Excel 2003 was already running) and the result was a bunch of gibberish (let’s see how this renders, but I doubt it really matters):
PK û¬
5ZÓ ;X, ParetoPlotter.xlsì
So I guess I have to file another bug report.
Thanks Jon,
It was a string array. I would have tried using a variant next, but I was being obstinate. I’m with you on the long/doubles advice.
Well, based on what you guys are telling me, it looks this is an issue with my PC’s configurations rather than an Excel issue. I won’t bother chasing it down because I’m lazy.
John, the dragging the zip file into Excel is a nifty little trick. I’ve never thought about dragging a file into Excel before.
Doug –
Again, I use variants for my own expediency, and I assume that other aspects of the UI of my projects eat up more cycles than Variants over more retentively-declared arrays (no offense, Dick). If the user spends five minutes deciding which of three items in a combo box to select, then if my code takes a few extra milliseconds (or seconds, really), what’s the rate-limiting step?
Thanks Jon,
I understand, and will be more open to variants in the future. It’s one of those things that I read when I was first learning VBA – “avoid variants where possible.” Of course, I think I read the same thing about ReDim.
Mostly I was being obstinate – I had been able to do something similar before, and I was trying to figure out why I couldn’t do it again. I suppose that’s a benefit of not being paid for my work – I’m free to waste as much time as I want ;)
I mentioned in the above post that I would be trying to mimic the template wizard with data tracking feature but with ADO on an Invoice template….over the weekend.
….Well finaly managed to get it working…today.
My thanks to XL-Dennis for the orginial code available on his website.
This code works on a TABLE like data (x rows and y columns) and writes this table to a closed file
I wanted this to work on discontinuos cellss…..managed it to get it working !!
Thanks
Sam
Hi
I have an add-in downloaded from internet. I want to rename it so that when it appears on the menubar alongwith other toolbars like “file,edit, view, isert, data, format,tools, window etc.” it appears with the new name I give to it. Any ideas !
Thanks and Regards
Amar
I am running office 2000 at home and I receive emails from a client who has the student and home ver of Office 2007. The client sends me excel files with the .XLSM extension, which I could not open in Office excel 2000. I went on the net and followed Microsoft’s instructions and downloaded the Compatibility Pack.Additionally I have all other updates properly downloaded, per Microsoft’ site. The site also said that for office 2000, nothing else is necessary to make these files accessible. I now can at least see the files (could not prior to the Compatibility Pack), however when I click on them I get the “wizard converter thing” and nothing I do at that point converts anything legible for me.
Any ideas.
Thanks
Rich
I think I am on to what the problem is on my computer, concerning the Compatibility Pack. It might be the HP Scan Jet 8250 software loaded. A friend is having the same problem, and has HP Scanning software loaded. He is trying to debug his now.