Using Rename action in Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gibs18 - 16 Dec 2005 21:24 GMT

I am using a macro to transfer Excel data into a table using the transfer
spreadsheet action. The transfer spreadsheet action requires a name for the
table. I am looking for a method that will prompt me (allow me) to rename
the table to anything I want instead.

Any ideas?

Reply to this Message

Ken Snell (MVP) - 17 Dec 2005 00:33 GMT

To enter your own text, use the InputBox function to show you a textbox into
which you can type your string. For the macro's Table Name argument, use an
expression similar to this:

=InputBox("Enter table name:")

Signature

Ken Snell
<MS ACCESS MVP>

>I am using a macro to transfer Excel data into a table using the transfer
> spreadsheet action. The transfer spreadsheet action requires a name for
[quoted text clipped - 3 lines]
>
> Any ideas?

Reply to this Message

John - 20 Dec 2005 14:48 GMT

Thank You, thats works perfectly. One more question though. Is there a way
to have the file name box prompt me to browse for the file instead of hard
coding it in?

>To enter your own text, use the InputBox function to show you a textbox into
>which you can type your string. For the macro's Table Name argument, use an
[quoted text clipped - 7 lines]
>>
>> Any ideas?

Reply to this Message

Ken Snell (MVP) - 20 Dec 2005 15:00 GMT

See http://www.mvps.org/access/api/api0001.htm for how to use the API
functions to do this.

Signature

Ken Snell
<MS ACCESS MVP>

> Thank You, thats works perfectly. One more question though. Is there a
> way
[quoted text clipped - 14 lines]
>>>
>>> Any ideas?

Reply to this Message

John - 20 Dec 2005 15:18 GMT

I wish I could. Unfortunately, I am a novice and its a bit to complex for me.
I assume there is nothing as easy as the response to my initial question that
can be entered directly into the file name field within the macro action??

>See http://www.mvps.org/access/api/api0001.htm for how to use the API
>functions to do this.
[quoted text clipped - 3 lines]
>>>>
>>>> Any ideas?

Reply to this Message

Ken Snell (MVP) - 20 Dec 2005 16:23 GMT

No, not already built into ACCESS (EXCEL has such a feature in its VBA, but
not ACCESS).

However, the way to use the API stuff is not that complicated. You need to
put all the code that you see in the article (in the light blue section,
under "code start" header) into a regular module (via the Modules window in
Database window). Then you just call the GetOpenFile function from your
macro in the same way you used InputBox.

Signature

Ken Snell
<MS ACCESS MVP>

>I wish I could. Unfortunately, I am a novice and its a bit to complex for
>me.
[quoted text clipped - 9 lines]
>>>>>
>>>>> Any ideas?

Reply to this Message

John - 20 Dec 2005 16:36 GMT

Thanks Ken, I will give it a try but I may have to keep posting until I have
it straight.

Is there anywhere that the VBA basics can be learned (a web-based tautorial
perhaps)?

>No, not already built into ACCESS (EXCEL has such a feature in its VBA, but
>not ACCESS).
[quoted text clipped - 10 lines]
>>>>>>
>>>>>> Any ideas?

Reply to this Message

Ken Snell (MVP) - 20 Dec 2005 18:33 GMT

I'm not aware of a VBA tutorial online... good books to get to get a start
on VBA would be the Beginning ACCESS 2002 VBA book by Smith and Sussman. The
2000 version of the book also is good.

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks Ken, I will give it a try but I may have to keep posting until I
> have
[quoted text clipped - 21 lines]
>>>>>>>
>>>>>>> Any ideas?

Reply to this Message

John - 20 Dec 2005 16:40 GMT

Is this the code you speak of:

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

>No, not already built into ACCESS (EXCEL has such a feature in its VBA, but
>not ACCESS).
[quoted text clipped - 10 lines]
>>>>>>
>>>>>> Any ideas?

Reply to this Message

Ken Snell (MVP) - 20 Dec 2005 18:32 GMT

No, look farther down on the web page. The code section of which I wrote
begins with these lines:

'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
lStructSize As Long

Signature

Ken Snell
<MS ACCESS MVP>

> Is this the code you speak of:
>
[quoted text clipped - 24 lines]
>>>>>>>
>>>>>>> Any ideas?

Reply to this Message

John - 20 Dec 2005 21:07 GMT

Ken, I can't belive how beautifully it worked. Thanks so much for your help!

>No, look farther down on the web page. The code section of which I wrote
>begins with these lines:
[quoted text clipped - 19 lines]
>>>>>>>>
>>>>>>>> Any ideas?

Reply to this Message

Ken Snell (MVP) - 21 Dec 2005 03:42 GMT

You're welcome.

Signature

Ken Snell
<MS ACCESS MVP>

> Ken, I can't belive how beautifully it worked. Thanks so much for your
> help!
[quoted text clipped - 22 lines]
>>>>>>>>>
>>>>>>>>> Any ideas?

Reply to this Message