Using Rename action in Macro

[Gibs18]

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?

 

[Ken Snell \(MVP\)]

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:")

 

[John via AccessMonster.com]

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
expression similar to this:

=InputBox("Enter table name:")

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?

 

[Ken Snell \(MVP\)]

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

Ken Snell
<MS ACCESS MVP>

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
expression similar to this:

=InputBox("Enter table name:")

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?

 

[John via AccessMonster.com]

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.

Thank You, thats works perfectly. One more question though. Is there a
way

[quoted text clipped - 14 lines]

 

[Ken Snell \(MVP\)]

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.

--

Ken Snell
<MS ACCESS MVP>

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.

Thank You, thats works perfectly. One more question though. Is there a
way

[quoted text clipped - 14 lines]

Any ideas?

 

[John via AccessMonster.com]

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).

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.

I wish I could. Unfortunately, I am a novice and its a bit to complex for
me.

[quoted text clipped - 9 lines]

 

[John via AccessMonster.com]

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).

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.

I wish I could. Unfortunately, I am a novice and its a bit to complex for
me.

[quoted text clipped - 9 lines]

 

[Ken Snell \(MVP\)]

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

--

Ken Snell
<MS ACCESS MVP>

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).

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.

I wish I could. Unfortunately, I am a novice and its a bit to complex
for
me.

[quoted text clipped - 9 lines]

Any ideas?

 

[Ken Snell \(MVP\)]

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.
--

Ken Snell
<MS ACCESS MVP>

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).

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.

I wish I could. Unfortunately, I am a novice and its a bit to complex
for
me.

[quoted text clipped - 9 lines]

Any ideas?

 

[John via AccessMonster.com]

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:

'***************** 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

Is this the code you speak of:

[quoted text clipped - 24 lines]

 

[Ken Snell \(MVP\)]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

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:

'***************** 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

Is this the code you speak of:

[quoted text clipped - 24 lines]

Any ideas?