Function on Switchboard button

[MonsterNewbie]

Hi

I am new to any programming and I have not been able to figure out how to
work with it yet so I am hoping that somebody can step me through this
process.

I would like a button on my switchboard to run code. I have a query that I
would like to export to Excel, but when I select run code in Edit Switchboard
Items it asks for a Function Name and then that's all.

How do I proceed from here?

Thank you very much.

 

[Klatuu]

Is it necessary in this case to run VBA code?
There is a TransferSpreadsheet Action you can call from a Macro and use the
Run Macro option from the Switchboard.

The main issue is whether the export will always go the the same file name
or whether you need to give the user an option on where to save the export.

As to running code from the Switchboard, you have to create a User Defined
VBA function in a standard module for the Run Code to use, but if you are not
familiar with VBA, this may not work for you.

Post back with some details, and let's see if we can help.

 

[MonsterNewbie]

Hi Dave:

The query is called qryExport. It does not need to be the same excel file
name every time. Actually, it would be wonderful if the user could specify
the file name and location each time they pushed the button.

Is it necessary in this case to run VBA code?
There is a TransferSpreadsheet Action you can call from a Macro and use the
Run Macro option from the Switchboard.

The main issue is whether the export will always go the the same file name
or whether you need to give the user an option on where to save the export.

As to running code from the Switchboard, you have to create a User Defined
VBA function in a standard module for the Run Code to use, but if you are not
familiar with VBA, this may not work for you.

Post back with some details, and let's see if we can help.

Hi

[quoted text clipped - 9 lines]

Thank you very much.

 

[MonsterNewbie via AccessMonster.com]

Hi Again

OK, I figured out the macro.

Is there a way for it to prompt me for the file name and location each time?
Also, it does not send the formatted query. When you export from within the
query you can send it as formatted and it looks way better.

Is there a way to do that?

Thanks for your help Dave.
Syl

Hi Dave:

The query is called qryExport. It does not need to be the same excel file
name every time. Actually, it would be wonderful if the user could specify
the file name and location each time they pushed the button.

Is it necessary in this case to run VBA code?
There is a TransferSpreadsheet Action you can call from a Macro and use the

[quoted text clipped - 13 lines]

 

[Klatuu]

No, you can't format the spreadsheet when using transferSpreadsheet. It can
be done using VBA and automation but this technique is pretty advanced.

The same is true for filenames; however, here is a trick to make it easy.

First, go to this site:

http://www.mvps.org/access/api/api0001.htm

Copy the code there and paste it into a standard module by itself. Name the
module modCommonDialog

Now, there are some minor changes to make:

Find the function name GetOpenFile

Comment out this code:

' lngFlags = ahtOFN_FILEMUSTEXIST Or _
' ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR


Change this:

strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
To:
strFilter = ahtAddFilterItem(strFilter, _
"Excel (*.xls)", "*.xls")

Now it will only show excel files instead of mdb or mda files.

Now it will work from the Macro.

In the Macro text box FileName, put this:

=GetOpenFile()

When you execute the Macro, it will pop up the Open File Dialog box, allow
the user to specify an output path and file name, and output the query.

Sorry, but the formatting will have to be done manually.
--
Dave Hargis, Microsoft Access MVP

Hi Again

OK, I figured out the macro.

Is there a way for it to prompt me for the file name and location each time?
Also, it does not send the formatted query. When you export from within the
query you can send it as formatted and it looks way better.

Is there a way to do that?

Thanks for your help Dave.
Syl

Hi Dave:

The query is called qryExport. It does not need to be the same excel file
name every time. Actually, it would be wonderful if the user could specify
the file name and location each time they pushed the button.

Is it necessary in this case to run VBA code?
There is a TransferSpreadsheet Action you can call from a Macro and use the

[quoted text clipped - 13 lines]

Thank you very much.

 

[MonsterNewbie via AccessMonster.com]

Wow, I was a little overwhelmed with all of that code but it worked!!

Thanks very much Dave, very , very much appreciated.
Syl

No, you can't format the spreadsheet when using transferSpreadsheet. It can
be done using VBA and automation but this technique is pretty advanced.

The same is true for filenames; however, here is a trick to make it easy.

First, go to this site:

http://www.mvps.org/access/api/api0001.htm

Copy the code there and paste it into a standard module by itself. Name the
module modCommonDialog

Now, there are some minor changes to make:

Find the function name GetOpenFile

Comment out this code:

' lngFlags = ahtOFN_FILEMUSTEXIST Or _
' ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR

Change this:

strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
To:
strFilter = ahtAddFilterItem(strFilter, _
"Excel (*.xls)", "*.xls")

Now it will only show excel files instead of mdb or mda files.

Now it will work from the Macro.

In the Macro text box FileName, put this:

=GetOpenFile()

When you execute the Macro, it will pop up the Open File Dialog box, allow
the user to specify an output path and file name, and output the query.

Sorry, but the formatting will have to be done manually.

Hi Again

[quoted text clipped - 20 lines]

 

[Klatuu]

Glad I could help.
I did not want to overwhelm you, that is why I included specific
instructions on how to make it work.
--
Dave Hargis, Microsoft Access MVP

Wow, I was a little overwhelmed with all of that code but it worked!!

Thanks very much Dave, very , very much appreciated.
Syl

No, you can't format the spreadsheet when using transferSpreadsheet. It can
be done using VBA and automation but this technique is pretty advanced.

The same is true for filenames; however, here is a trick to make it easy.

First, go to this site:

http://www.mvps.org/access/api/api0001.htm

Copy the code there and paste it into a standard module by itself. Name the
module modCommonDialog

Now, there are some minor changes to make:

Find the function name GetOpenFile

Comment out this code:

' lngFlags = ahtOFN_FILEMUSTEXIST Or _
' ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR

Change this:

strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
To:
strFilter = ahtAddFilterItem(strFilter, _
"Excel (*.xls)", "*.xls")

Now it will only show excel files instead of mdb or mda files.

Now it will work from the Macro.

In the Macro text box FileName, put this:

=GetOpenFile()

When you execute the Macro, it will pop up the Open File Dialog box, allow
the user to specify an output path and file name, and output the query.

Sorry, but the formatting will have to be done manually.

Hi Again

[quoted text clipped - 20 lines]

Thank you very much.