ita, did you get the spreadsheet import working?

My boss tried this VB code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "test", "C:PayrollRegister_PE4-4-2008.xls", , "AN3!A1:I100"

...where "AN3" is the sheet name. As long as the Range argument is the same size or bigger than the the data in the sheet, it works OK.

Also, do you know how to call the Windows Common Dialog control (i.e. the thing that lets you browse for the file) from Access?

I basically put those into the actions in the macro, and it worked fine.

Can you force a Windows Common Dialog control in a macro?

No, wait...the worksheet names change every week too--it's a datestamp. It's weird. I'm so far away from VB these days. It's a simple thing--a form with an input field of a datestamp, and a big old button, that when pressed constructs a filename and two worksheet names and goes and imports the data.

I just gotta dip my toe back into the water.

Still, even with the macro needing editing by hand every time it's faster than doing it manually every week. So I may not need to broach my VB fear.

Can you force a Windows Common Dialog control in a macro?

Nope. Well, there is an OCX control that might work with a macro, but it sucks. We use VB to do a Windows API call. Sounds messy, but ya' just need to copy a couple lines of code....

Ick.

Okay, so I dipped into forms again. Looks like they're pandering to me, so I might just get out of this alive. First things first--can I pass an argument (aka build a filename) to a macro? Or should I just go straight to DoCmd? I should just go straight.

Actually, looking at this, I should have been in here earlier. I'd thought the learning curve of getting back in would be higher. But they're totally talking down to me.

can I pass an argument (aka build a filename) to a macro?

Nope.

Or should I just go straight to DoCmd? I should just go straight.

Yeah, it's really not that bad....

You say that, and I'm just staring here at a screen that is pretty much *begging* me to call a macro with that button. Let me see...how do I concatenate strings again?

The light! It burns!

Let me see...how do I concatenate strings again?

Was that rhetorical?

Anyway, use &

Was that rhetorical?

More... melodramatic.

And, we're done! Ugly little bugger, but spending much more time on it would tip the scales into the time-wasting end of things. Does what it needs to do.

Thanks!

I am now posting from my Windows machine on my MacBook Pro. Heh heh heh.

Better patch my security holes, quick.

MVC is dreamy.

If you'd like to see the patterns that you might might like even better than MVC, I highly recommend Patterns of Enterprise Application Architecture , by Fowler. It has seven of what Fowler calls "Web Presentation Patterns", of which MVC is just one. It talks about why you might want to use one pattern over another, depending on your requirements.

You also get the money pattern thrown in for free!