 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Macros / June 2006Can a macro ask for a file name ?
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 Sure - in the Table Name argument of the macro, type this expression: =InputBox("Enter name of table to be exported:", "TableName")  SignatureKen Snell <MS ACCESS MVP> >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 [quoted text clipped - 3 lines] >table I tell it ? This way I can have one macro to manage the export of 20 >tables, instead of 20 macros !... thanks ... 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 > Sure - in the Table Name argument of the macro, type this expression: > [quoted text clipped - 7 lines] >>table I tell it ? This way I can have one macro to manage the export of 20 >>tables, instead of 20 macros !... thanks ... 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 [quoted text clipped - 4 lines] > > thanks ... Roger Have you tried =[TableName] & InputBox("Enter number of the table to be exported:", "TableNumber") Tom Lake 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 [quoted text clipped - 12 lines] > > Tom Lake 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? SignatureKen 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 [quoted text clipped - 19 lines] >> >> Tom Lake 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 [quoted text clipped - 32 lines] >>> >>> Tom Lake ="repertorium" & InputBox("Enter name of table to be exported:", "TableName") SignatureKen 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, [quoted text clipped - 43 lines] >>>> >>>> Tom Lake Sorry : ="repertorium" & InputBox("Enter number of table to be exported:", "TableName") SignatureKen Snell <MS ACCESS MVP> > ="repertorium" & InputBox("Enter name of table to be exported:", > "TableName") [quoted text clipped - 46 lines] >>>>> >>>>> Tom Lake Great ! Thanks Roger > Sorry : > [quoted text clipped - 52 lines] >>>>>> >>>>>> Tom Lake |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.