Increment default InPutBox value

RWayneH
RWayneH used Ask the Experts™
on
I have an inputbox that has a default value of today date and I would like to start incrementing it instead having it the same each time.  There is a small issue that when I use my Sub Save_XML it is called from within another Sub that is running a Do While loop.  If the InputBox is going to increment now, I will need to pass a variable from one sub to the other in order to keep a counter correct.  Ideally I would like to increment the default text in the InputBox to "File001", "File002"  ....

So two questions in one here.  One how do I make a variable global so it can be used in a called sub from a sub?  Two how would I change the code below to use that variable to increment the InputBox default value?

Please advise and thanks.

Sub Save_XML()
    Dim strFilePath, strFileName As String
    
    strFilePath = "C:\_SAP\LynxFiles\" 'This needs to be changed to correct directory and must end with \
    'strFilePath = gstDestinationFolder
    
    strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                "TestFile-" & Format(Now(), "yyyy-mm-dd")) '<--- change to new default

    If strFileName = "" Then
        Sheets("SIF Data").Select
        ActiveWindow.SelectedSheets.Visible = False
        Sheets("Order Line Items").Select
        ActiveWindow.SelectedSheets.Visible = False
    
        Exit Sub 'Will happen if Cancel is pressed
    End If
    strFileName = strFileName & ".xml"
    
    Application.DisplayAlerts = False
    ActiveSheet.Copy
    
    ActiveWorkbook.SaveAs Filename:= _
        strFilePath & strFileName, FileFormat:=xlTextPrinter, CreateBackup:=False
    ActiveWorkbook.Close savechanges:=False
    
    Application.DisplayAlerts = True

     Sheets("MasterCopy").Select
    
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
-change the Save_XML
with
  sub Save_XML(strFile as string)
     'use the strFile as the strFileName
strFileName=strFile

in the Sub that is running a Do While loop, create a string variable

dim str as string, j as variant

do while
         str="File"
         j=Format(j+1,"000")
        str=str & j

'your code here
       call Save_XML(str)
loop

Open in new window

Author

Commented:
Ok... while debugging..  I have a Sub with a do while, that calls another sub, that has the call to the code above...  probably not the best structure, but when it gets the InputBox, the values are Empty...  Is there a way to make a variable truly global so it can be used across a Sub that calls a sub that calls a sub ......   If not I will have to look at restricting the subs.

Author

Commented:
Even when I copied the Save_XML out of its own sub and moved it one layer up, they are still Empty.  It changes worksheets and as soon as the worksheet tab changes the values disappear.  How do I hold the variable value to use in the InputBox?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
<How do I hold the variable value to use in the InputBox? >
why is the need for the input box when you already have a value for "strFileName"

just comment or remove the line with Inputbox

Author

Commented:
I think we are close, because it is keeping the values now..  File001  002 ....  but when the InputBox shows it is blank...  What did I screw up in the InputBox statement to show?

   strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                str) '<--- change to new default

Author

Commented:
To answer your other question, I will use it with an InputBox and without.  We want the user to be able to edit the default name or leave it as is.
Top Expert 2016

Commented:
to declare a global variable,
 remove the declaration inside the Sub
declare it at the top of any regular moduleoption explicit

Global str as string

Author

Commented:
All is good, except for getting it to show in the InputBox...  It is blank, but str is defined correctly.  How would I edit the following to work with the variable str?

strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                 str) '<--- change to new default
Top Expert 2016

Commented:
what did you put in the sub Save_XML(??)

try using
sub Save_XML(str)

strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                  str)

Author

Commented:
Figured it out....  I was getting an error in the Call Save_XML(str).  So I took that out.  Putting in a True/False to ask user if they want to use edit file names or use suggested default...  Thanks for the help...  I am still trying to grab the global variable concept... so I can press them from sub to sub...  I like calling to existing Subs that I write for specific things, then do a lot of calling to them, (reduces rewrites and copy/pasting) but if I am going to continue doing that... I want to define globals more than not.
Top Expert 2016

Commented:
< I was getting an error in the Call Save_XML(str).  >
what is the error?

Author

Commented:
It was a Compile Error:  Wrong number of arguments or invaled assignment.  Is it because your was Sub Call Save_XML(str) and mine had Call Save_XML(str)?   This is what is calling the already defined str value right?
Top Expert 2016

Commented:
<Is it because your was Sub Call Save_XML(str)> where did you get this?

Author

Commented:
Thank you for the help, I really appreciate it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial