Open a browser box for the location of a .txt file
Put that data into an Access table in the format I specify
Open a browser box for another .txt file
Add that data into the same table
Create and display a specific Pivot format
I’ve got no problem integrating vba into an Access 2010 macro, since that should be able to handle some parts better. My problem is that I’m a total neophyte at VBA, if not coding in general.
I’m not looking for code examples. That’s the problem: I can find a million code examples, but I can only partially understand them. I can’t figure out the syntax, or what’s required and what can be dropped. And most of the code examples are ludicrously complex and incorporate all kinds of stuff I don’t need or want. However, if you have some code you want to share, I don’t mind, either.
What I am after is a good reference which doesn’t spend fifty tutorials on opening VBA script and actually explains the syntax, references, and what certain things do.
How ought I use variables in a Macro? I can create one, except it errors out (seriously, is it that hard to just set a variable = [Date$] :rolleyes: ). Then I see no way to use that in another area of the macro (such as the name of the table I wish to create).
Geez, I thought Access 2010 macros were supposed to be easier to use. Right now it’s a warren of unexplained problems, complications they don’t explain, code all the old-timers know and thus never bother to explain, and general lack of explanations.
If you are trying to declare a variable it needs to be done with a dimension statement which is best done before the subroutine is started.
Example
Dim LName as String, CountA as integer
You can also declare variables with special characters
Example
Dim LName$, Counta%
is the same as the first example
I do a lot of VBA programming in Excel and I got a lot of help from one of the many dummies books (Excel VBA Programming for Dummies). You might want to pick up that book or possibly one that specializes in Access projects.
I don’t think the macro builder in Access 2010 will create a table, but code like this will do it:
Sub createtable()
txtDate = Date$
Set dbs = CurrentDb
Set tbl = dbs.CreateTableDef(txtDate)
Set fld = tbl.CreateField("Field1", dbText, 255)
tbl.Fields.Append fld
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh
End Sub
VBA (like any programming language) requires knowledge of and strict adherence to syntax and vocabulary, gained though study and practice. And a lot of patience
Do you need this done in Macros? I do all my Access VBA in modules. Macros can be handy for swiping a quick bit of code for something simple, but I find them to be more trouble than worth for much else.
Do you just want code for one task, or to actually learn VBA?
I’ve got code for what you’re doing, although I’m only now in the process of converting it for 2010. Even as we speak, I’m working on that.
It’s getting all those pieces in that can take a bit of code.
I don’t know of a way to define the import specifications other than to walk through the Import Wizard and save the specs there. Once you’ve saved them with a name, then you can use them in code.
To let the user select the file, look at the FileDialog object. (I just finished converting this part of my code, so let me know if you need help.)
I’m currently using (and am kinda stuck with) the 2010 Macro Expression Wizard. It’s very odd, in that it wants to do about half of the work for you. However, it’s icky about which half, and about what data is tells you. The interface feels like it was designed for people who don’t code, but has all these quirks and requirements that I have to dig through seven boatloads of online documention to discover.
VBA script isn’t easy, but it’s far more straightforward, and makes much more sense to me having a little C coding in the background. Its big problem is that it occupies a halfway space between an object-oriented programming language and SQL. But the reason I was trying to learn Access macros was so that other people could, in the future, more easily alter the code without needing to know how to code.
I may just end up slapping something together with VBA. In fact, half of it already is (although my code is… inelegant). And I’m sure I have a passel of mistakes since I haven’t tested yet.
Yes, yes it is. It’s not really one or the other, just some weird bastard step-child of a language. My first experience with OO programming was an early version of VB, which put me right off the whole concept for a long time.
And the macros are worse. I think they were trying to follow the other Office products, where you can “record” a manual process and it auto-magically turns into VBA with a button to click to run it. But they couldn’t make that work, so they did this weird “click and select then fill in details” thing that doesn’t really work unless you already know VBA.
Oh, and then there’s the fact that Access VBA documentation has sucked giant greasy gophers since it came out in what, '95? Warning: if you’re copying & pasting sample code from the docs, don’t be surprised when it doesn’t work. It’s a crapshoot.
You can write VBA and just use macros to run it, but that doesn’t solve your problem of making it easy for non-coders.
I’ve opted to do everything in VBA and be responsible for upkeep unto eternity. If I get eaten by wolves, they’ll just have to figure something out.
Good luck with your project. Let me know if you get stuck on something.
That info is also on tap from the VBA development screen itself when you click on the Help button (question mark). You probably want to drill down into VBA Language Reference.
I figured they were trying to bring scripting to people who know nothing of coding, which sounds alike a good plan. However, they kept all the awkward rules from VBA without explaining any of them. To me, it looks as if they’d gone one step further, they’d have a really solid foundation. But then, natural language programming has been “in the works” for my entire life.
headdesk
Suddenly so many things make much more sense now.
I’m actually thinking it might be faster and leave fewer marks on my forehead to use SQL. While it’s messy, the alternative is is to either use a Macro which works but nt you think, or vba which no one understands. There’s at least one other person who can definitely help with the SQL work and can alter anything if needed, so that’s my plan now.
Ah, I see you’ve tried those sample codes. Yeah, took me a while to figure that out, too. Doesn’t help that people will repost it on their pages like they’ve actually used it.
Usually it’s a matter of reformatting it a bit. Is the input in parentheses? Try quotes instead. Or vice-versa. Or quotes inside the parens. Or …
It’s insane.
I mostly just comment the hell out of my code. Even if someone doesn’t know VBA, they’ll be able to understand what each step is doing. We’ve got folks smart enough to figure it out from there.
It’s an old habit, from my earliest days programming, when I was tasked with revising a couple of key points in someone else’s completely undocumented Fortran IV program. (Oh look, we’ve got an intern thingy. Someone find something for it to do! I know, what about that program of Fred’s we’ve wanted to fix for years but no one is willing to touch? That’s a great idea!)
