Can a macro ask for a file name ?

[Roger]

I have Access2002sp3 on WinXP. I have a database where I regularly want to
export a table of data to excel. The table concerned varies, and I have
always written a new macro for each table, even though the sequence (or
code) is the same as the last, just a different table name. Is there any way
I can get the macro to ask for the table name, and then export the table I
tell it ? This way I can have one macro to manage the export of 20 tables,
instead of 20 macros !... thanks ... Roger

 

[Ken Snell \(MVP\)]

Sure - in the Table Name argument of the macro, type this expression:

=InputBox("Enter name of table to be exported:", "TableName")

 

[Roger]

Great, many thanks, it works just fine. Just to add a little more
sophistication to the macro, all of my tables to be exported have the same
name with a number. I tried "Fixedname"&"TableNumber" as the file number ...
the "TableNumber" being the input ... but it doesn't work as a normal string
.... do I need to define the fixed name part of the file name on another line
somehow ? The Access macros aren't quite the same as Excel !

thanks ... Roger

 

[Tom Lake]

Great, many thanks, it works just fine. Just to add a little more
sophistication to the macro, all of my tables to be exported have the same
name with a number. I tried "Fixedname"&"TableNumber" as the file number
... the "TableNumber" being the input ... but it doesn't work as a normal
string ... do I need to define the fixed name part of the file name on
another line somehow ? The Access macros aren't quite the same as Excel !

thanks ... Roger

Have you tried

=[TableName] & InputBox("Enter number of the table to be exported:",
"TableNumber")

Tom Lake

 

[Roger]

Yes, but it looks for the TableName first (which is the common name element
of all tables) before asking for input ... and as it cannot find the
TableName (since it has no number to complete the name) I get an error
message.

Roger

Great, many thanks, it works just fine. Just to add a little more
sophistication to the macro, all of my tables to be exported have the
same name with a number. I tried "Fixedname"&"TableNumber" as the file
number ... the "TableNumber" being the input ... but it doesn't work as a
normal string ... do I need to define the fixed name part of the file
name on another line somehow ? The Access macros aren't quite the same as
Excel !

thanks ... Roger

Have you tried

=[TableName] & InputBox("Enter number of the table to be exported:",
"TableNumber")

Tom Lake

 

[Ken Snell \(MVP\)]

So you want the macro to ask for the table name once, and then export all
the tables that start with that table name excerpt? That is a bit trickier
to do with a macro, but can be worked out.

How will the macro know which number sequence to use? Give us more details
about exactly what you want to do.

Also, note that an ACCESS macro is not like an EXCEL macro. EXCEL macros are
actually VBA code; ACCESS can be programmed with VBA but we call it "VBA
code" and not macros. ACCESS macros are just simple script steps.

Are you familiar with VBA?

--

Ken Snell
<MS ACCESS MVP>

Yes, but it looks for the TableName first (which is the common name
element of all tables) before asking for input ... and as it cannot find
the TableName (since it has no number to complete the name) I get an error
message.

Roger

Great, many thanks, it works just fine. Just to add a little more
sophistication to the macro, all of my tables to be exported have the
same name with a number. I tried "Fixedname"&"TableNumber" as the file
number ... the "TableNumber" being the input ... but it doesn't work as
a normal string ... do I need to define the fixed name part of the file
name on another line somehow ? The Access macros aren't quite the same
as Excel !

thanks ... Roger

Have you tried

=[TableName] & InputBox("Enter number of the table to be exported:",
"TableNumber")

Tom Lake

 

[Roger]

I understand some VBA, mainly in EXCEL which (as you say) is different to
ACCESS. I have, however, some competence with ACCESS. The table names are,
for example, repertorium1, repertorium2, repertorium3, and so on. I want to
embody the word "repertorium" in the code with the inputbox prompt simply
being "what report number ?". Then the number is joined to the word
repertorium (which never changes), to become the table name. For example,
"repertorium" is in the code, the number 19 is input, and the table name is
"repertorium19".

thanks for your help ... Roger

So you want the macro to ask for the table name once, and then export all
the tables that start with that table name excerpt? That is a bit trickier
to do with a macro, but can be worked out.

How will the macro know which number sequence to use? Give us more details
about exactly what you want to do.

Also, note that an ACCESS macro is not like an EXCEL macro. EXCEL macros
are actually VBA code; ACCESS can be programmed with VBA but we call it
"VBA code" and not macros. ACCESS macros are just simple script steps.

Are you familiar with VBA?

--

Ken Snell
<MS ACCESS MVP>

Yes, but it looks for the TableName first (which is the common name
element of all tables) before asking for input ... and as it cannot find
the TableName (since it has no number to complete the name) I get an
error message.

Roger

Great, many thanks, it works just fine. Just to add a little more
sophistication to the macro, all of my tables to be exported have the
same name with a number. I tried "Fixedname"&"TableNumber" as the file
number ... the "TableNumber" being the input ... but it doesn't work as
a normal string ... do I need to define the fixed name part of the file
name on another line somehow ? The Access macros aren't quite the same
as Excel !

thanks ... Roger

Have you tried

=[TableName] & InputBox("Enter number of the table to be exported:",
"TableNumber")

Tom Lake

 

[Ken Snell \(MVP\)]

="repertorium" & InputBox("Enter name of table to be exported:",
"TableName")

--

Ken Snell
<MS ACCESS MVP>

I understand some VBA, mainly in EXCEL which (as you say) is different to
ACCESS. I have, however, some competence with ACCESS. The table names are,
for example, repertorium1, repertorium2, repertorium3, and so on. I want to
embody the word "repertorium" in the code with the inputbox prompt simply
being "what report number ?". Then the number is joined to the word
repertorium (which never changes), to become the table name. For example,
"repertorium" is in the code, the number 19 is input, and the table name is
"repertorium19".

thanks for your help ... Roger

So you want the macro to ask for the table name once, and then export all
the tables that start with that table name excerpt? That is a bit
trickier to do with a macro, but can be worked out.

How will the macro know which number sequence to use? Give us more
details about exactly what you want to do.

Also, note that an ACCESS macro is not like an EXCEL macro. EXCEL macros
are actually VBA code; ACCESS can be programmed with VBA but we call it
"VBA code" and not macros. ACCESS macros are just simple script steps.

Are you familiar with VBA?

--

Ken Snell
<MS ACCESS MVP>

Yes, but it looks for the TableName first (which is the common name
element of all tables) before asking for input ... and as it cannot find
the TableName (since it has no number to complete the name) I get an
error message.

Roger


Great, many thanks, it works just fine. Just to add a little more
sophistication to the macro, all of my tables to be exported have the
same name with a number. I tried "Fixedname"&"TableNumber" as the file
number ... the "TableNumber" being the input ... but it doesn't work
as a normal string ... do I need to define the fixed name part of the
file name on another line somehow ? The Access macros aren't quite the
same as Excel !

thanks ... Roger

Have you tried

=[TableName] & InputBox("Enter number of the table to be exported:",
"TableNumber")

Tom Lake

 

[Ken Snell \(MVP\)]

Sorry :

="repertorium" & InputBox("Enter number of table to be exported:",
"TableName")

--

Ken Snell
<MS ACCESS MVP>

="repertorium" & InputBox("Enter name of table to be exported:",
"TableName")

--

Ken Snell
<MS ACCESS MVP>

I understand some VBA, mainly in EXCEL which (as you say) is different to
ACCESS. I have, however, some competence with ACCESS. The table names are,
for example, repertorium1, repertorium2, repertorium3, and so on. I want
to embody the word "repertorium" in the code with the inputbox prompt
simply being "what report number ?". Then the number is joined to the word
repertorium (which never changes), to become the table name. For example,
"repertorium" is in the code, the number 19 is input, and the table name
is "repertorium19".

thanks for your help ... Roger

So you want the macro to ask for the table name once, and then export
all the tables that start with that table name excerpt? That is a bit
trickier to do with a macro, but can be worked out.

How will the macro know which number sequence to use? Give us more
details about exactly what you want to do.

Also, note that an ACCESS macro is not like an EXCEL macro. EXCEL macros
are actually VBA code; ACCESS can be programmed with VBA but we call it
"VBA code" and not macros. ACCESS macros are just simple script steps.

Are you familiar with VBA?

--

Ken Snell
<MS ACCESS MVP>


Yes, but it looks for the TableName first (which is the common name
element of all tables) before asking for input ... and as it cannot
find the TableName (since it has no number to complete the name) I get
an error message.

Roger


Great, many thanks, it works just fine. Just to add a little more
sophistication to the macro, all of my tables to be exported have the
same name with a number. I tried "Fixedname"&"TableNumber" as the
file number ... the "TableNumber" being the input ... but it doesn't
work as a normal string ... do I need to define the fixed name part
of the file name on another line somehow ? The Access macros aren't
quite the same as Excel !

thanks ... Roger

Have you tried

=[TableName] & InputBox("Enter number of the table to be exported:",
"TableNumber")

Tom Lake

 

[Roger]

Great ! Thanks

Roger

Sorry :

="repertorium" & InputBox("Enter number of table to be exported:",
"TableName")

--

Ken Snell
<MS ACCESS MVP>

="repertorium" & InputBox("Enter name of table to be exported:",
"TableName")

--

Ken Snell
<MS ACCESS MVP>

I understand some VBA, mainly in EXCEL which (as you say) is different to
ACCESS. I have, however, some competence with ACCESS. The table names
are, for example, repertorium1, repertorium2, repertorium3, and so on. I
want to embody the word "repertorium" in the code with the inputbox
prompt simply being "what report number ?". Then the number is joined to
the word repertorium (which never changes), to become the table name. For
example, "repertorium" is in the code, the number 19 is input, and the
table name is "repertorium19".

thanks for your help ... Roger

So you want the macro to ask for the table name once, and then export
all the tables that start with that table name excerpt? That is a bit
trickier to do with a macro, but can be worked out.

How will the macro know which number sequence to use? Give us more
details about exactly what you want to do.

Also, note that an ACCESS macro is not like an EXCEL macro. EXCEL
macros are actually VBA code; ACCESS can be programmed with VBA but we
call it "VBA code" and not macros. ACCESS macros are just simple script
steps.

Are you familiar with VBA?

--

Ken Snell
<MS ACCESS MVP>


Yes, but it looks for the TableName first (which is the common name
element of all tables) before asking for input ... and as it cannot
find the TableName (since it has no number to complete the name) I get
an error message.

Roger


Great, many thanks, it works just fine. Just to add a little more
sophistication to the macro, all of my tables to be exported have
the same name with a number. I tried "Fixedname"&"TableNumber" as
the file number ... the "TableNumber" being the input ... but it
doesn't work as a normal string ... do I need to define the fixed
name part of the file name on another line somehow ? The Access
macros aren't quite the same as Excel !

thanks ... Roger

Have you tried

=[TableName] & InputBox("Enter number of the table to be exported:",
"TableNumber")

Tom Lake