Trying to create a VBA function to export an array of tables with date notation

[Gobes]

Hello all,

I am building an Access database to automate some processes in the office.

What I would like to do is after running some daily queries, to export all tables created with a date and time stamp.

I know this is a much asked feature because I can find many similar questions on the internet but since I am a complete newbie in using VBA code I cannot get it to work so now I am looking for help.

First I have tried to export only one table. I have created a sub like this:

Public Sub uitvoer()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_uitvalbak", "F:\debiteuren\Gijs\tbl_uitvalbak_" & Format(Date, "yyyymmdd") & ".xlsx"
End Sub

Since I need a function to use in my macro I then created a function and call the sub in there. Probably it doesn't make any sense but as said, I have no knowledge of vba and was just playing around. Like this:

Public Function uitvoeren()
Call uitvoer


End Function

Now the module looks like this in total:

Option Compare Database

Public Function uitvoeren()
Call uitvoer
End Function

Public Sub uitvoer()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_uitvalbak", "F:\debiteuren\Gijs\tbl_uitvalbak_" & Format(Date, "yyyymmdd") & ".xlsx"
End Sub


If I run this in the VBA editor it works, but if I call the function from a macro it doesn't find the arguments and says I need to specify argument for the transferspreedsheet command.

So how do I provide the transferspreadsheet command with the required arguments?

When I finally get this to work I want to expand the function to be able to export an array of tables, like a 'for each' loop.

I would love to get some ideas from you guys on how to do this.

Many thanks!

[June7]

What macro is calling the function? What triggers the macro? Why use a macro that calls a Function that calls a Sub? Why not just VBA since you already have it there? I don't use macros but I tested a setup like yours and it works.

Looping code with macro is hard to do. I figured it out once and don't recommend it. VBA much better. Where would the 'array' of tables be?

[Gobes]

Well, this is the first time I am trying to do something in vba code. I feel that this is where the power is, so I want to learn it.

The macro is activated from a switchboard by the user. The macro only runs some queries in sequence and after it has done that I want it to run the code to export the created tables.

In a macro I believe I can only call a function and not a sub, and if I put the DoCmd in just a function it seems to not work at all anymore. I want to code the 'loop' in vba. The array would just be a fixed selection of tables in the same database. How I thought of it was something like:

Dim tables(4) as ? `don't know what i should use here

table (0) = "tbl_customers_amt_due"
table (1) = "tbl_customers_paid"
table (2) = "tbl_payments"
etc..

For each ... in tables
DoCmd.TransferSpreadsheet
Next

Something like this.

[June7]

Yes, macros can call only functions. Then the function can call the sub. It does work in my test. However, I would just put everything in VBA, even running the daily queries (what do those queries accomplish - import, update, delete?).

You are using a Switchboard? Never liked, never used - Switchboard uses macros.

[Gobes]

Yes, the queries import some data and then with the use of imported data they manipulate some values in other tables. If I want to seriously start out and learn VBA, can you recommend any resources?

[Gobes]

I got the initial setup to work Big progress NOw I am going to try and figure out how to export tables stored in an array and deliver different arrays to the function when I call it so I can use the same module to export different arrays of tables.

[June7]

Check out 'Access™ 2007 Programming by Example with VBA, XML, and ASP' by Julitta Korol, Wordware Publishing, Inc.