Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursive Sub Routine?
I have a question for you gurus out there!
I have a macro to import a text file, run some code and basically assign item numbers to each part of sheet based on different cells values. When first run, the macro prompts for a job number, and then creates a directory for this job number. Because each job has multiple text files, at the end of each run of the subroutine, i'm calling the "WallsPlus()" sub routine again. The thing is, I don't want the user to be prompted to have to type in a job number for every single text file (Sometimes 50+) I have two ideas, neither of which I know how to implement. 1. I wanted to setup If strJobNumber = "" then prompt for the job number... if strJobNumber < "" then don't ask for a job number. The problem here, i believe, is that when the sub routine is recalled, the previous strJobNumber is erased and is always set to "" 2. Import all the text files in one go. I don't see how this is a huge possibility, simply because every way i have tried to import a new text file, it opens a new workbook. And to create a macro to copy and paste a new workbook into a previous workbook an unknown number of times seems rather difficult and necessary. If anyone has any ideas on how I can implement either of these solutions, let me know, and if you want to see my code (although very crude, but working none-the-less) let me know! Thanks, Derek |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursive Sub Routine?
Thank you very much! Works exactly as desired! I very much appreciate it!
I have one more question regarding variables. In another macro I use to sort by multiple categories and calculate statistics uses a userform with 12 checkboxes. I was wondering if there was a way to allow the status of these checkboxes to be accessed by other subroutines to avoid having to do: Call Sort(checkbox1, checkbox2, checkbox3...checkboxN) I have the function working fine by passing the variables this way, but I thought it would be easier in the future if there was a way to make variables available to all subs. I saw something about this one time, something about a common variable, but I haven't been able to find it since. I've tried playing with the global, public, and private types, but had no luck! Thanks Rick! Derek "Rick Rothstein" wrote: Inside the WallsPlus subroutine, declare the variable that holds the job number as a Static String so that its value will survive successive call to the subroutine and then just test to see if the variable's content is the empty or not. For example, put the following in the code window for one of your worksheets... Sub TestJobNumber() Static JobNumber As String If JobNumber = "" Then JobNumber = InputBox("What is the Job Number?") End If MsgBox "The Job Number is " & JobNumber End Sub Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Cancel = True TestJobNumber End Sub Now go to that worksheet and double click any cell. You will be prompted for a Job Number. Type it in and hit the Enter key. Doing that will display the Job Number back to you. Dismiss the dialog box. Now, double click any other cell. This time, you will not be prompted for the Job Number; instead, the Job Number you typed in earlier will be repeated back to you. -- Rick (MVP - Excel) "Derek Johansen" wrote in message ... I have a question for you gurus out there! I have a macro to import a text file, run some code and basically assign item numbers to each part of sheet based on different cells values. When first run, the macro prompts for a job number, and then creates a directory for this job number. Because each job has multiple text files, at the end of each run of the subroutine, i'm calling the "WallsPlus()" sub routine again. The thing is, I don't want the user to be prompted to have to type in a job number for every single text file (Sometimes 50+) I have two ideas, neither of which I know how to implement. 1. I wanted to setup If strJobNumber = "" then prompt for the job number... if strJobNumber < "" then don't ask for a job number. The problem here, i believe, is that when the sub routine is recalled, the previous strJobNumber is erased and is always set to "" 2. Import all the text files in one go. I don't see how this is a huge possibility, simply because every way i have tried to import a new text file, it opens a new workbook. And to create a macro to copy and paste a new workbook into a previous workbook an unknown number of times seems rather difficult and necessary. If anyone has any ideas on how I can implement either of these solutions, let me know, and if you want to see my code (although very crude, but working none-the-less) let me know! Thanks, Derek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recursive Functions...maybe | Excel Worksheet Functions | |||
Recursive Sub Routine? | Excel Programming | |||
Is this a recursive problem? | Excel Programming | |||
Recursive Code | Excel Programming | |||
Recursive Functio help | Excel Worksheet Functions |