How to use InputBox, accepting initial input and avoid the MsgBox.

    This is a portion of a macro that checks for an existing filename, then just uses a +1 formula to select the next number in 3 digits, IF it's already been used.


    i.e. ...345.csv to ...346.csv.


    I just never change the number to anything other than the NEXT number as above, so I'd like to SKIP the MsgBox and I guess just default to OK without clicking on it.


    The only worry is whether it would move another +1 number IF (in the example above) 346.csv also existed, automatically moving to ...347.csv. :)






    Thank you for this little twist!

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    maqybe ..


    or look at this link
    https://www.thespreadsheetguru…on-if-file-already-exists

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Thanks, I looked at the link. Thing is that this code already looks whether the file exists and even increases the 3 digit file name[number] by 1. I just would like to avoid the msgbox popup. As long as it will increase by a 1 or 2 (just in case both identical file names exist) there's no need to verify this using the msgbox.


    In your code 'mycount' is undefined. :(


    Here's the entire macro code, just in case that helps. :)



    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Solution inside *****************************



    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    No problem! Glad to help.


    Now if I can just get this fix to work on another, similar, macro! Trouble is that it's proprietary, so I'm not able to post it. :( Maybe I could post 'part' of it.


    Quote from pike;785922

    oops missed your last reply but well done . thanks for posting your adaptation of the do loop/solution

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Ok. I hope it's ok to keep here, because it relates so much to the main topic.


    This is 'about' what we're trying to replace 'fix' with the new code previously mentioned.


    The big issue is that unlike the last macro that runs the fix and then closes the wbs, this macro writes the file, then does a whole different section to format that file and it keeps erroring. I'll post those next.





    More of the code in context...



    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    First, I noticed that this code, part of which new code fix replaces, never occurs.


    Code
    If rply = "" Then
             MsgBox "File name is not valid"
             wk2.Delete
             wb.Close
             Exit Sub



    So, I thought maybe these are not needed, certainly wk2.Delete, plus they're used at the end of the macro as all active wks and wb close.



    Code
    wk2.Delete
     wb.Close



    When I remove both and keep wb.SaveAs FileName:=strFile & lFileNumber & ".vcf"





    The macro then errors on this line: Open OutFilePath For Output As FileNum... PROGRESS!


    Then I thought maybe the file needs to be closed after creating, so I added wb.Close under the wb.SaveAs line.





    But then it errors back on the original line Set wk = Worksheets(2)

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    and the file naming convention


    how about something like...

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    should be


    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Thanks so much! I'll start with this one.


    Unless I'm not seeing it correct. This part just never pops up. (MsgBox "File name is not valid")


    And earlier I had changed this line wk1.Delete in the original "fix" to wk2.Delete, then I was thinking I don't need it at all, so I removed it. ??


    Since it's not used, I wouldn't need this code, right?


    Code
    If rply = vbCancel Or rply = vbNullString Then


    This is the code that pops up a lot that I'm trying to avoid...


    Code
    rply = InputBox("File " & fn1 & vbCrLf & "already exist, Would you like to change this file" & vbCrLf & "with following available name or change name as you like", "VCF File Name", pth & "\" & fn2 & ".vcf", vbOKCancel)



    Quote from pike;785935

    Hmmm
    what do you mean rply never occurs? when you press cancel or there is no value in the input box?
    maybe

    Code
    If rply = vbCancel Or rply = vbNullString Then


    but the second bit

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Here's a few things I saw trying your new code.


    1) Filename created was instead of i.e. SatFeb18-815.vcf exists so use this filename SatFeb18-816.vcf; yours uses SatFeb18-8151.vcf, then if run again SatFeb18-8152.vcf and so on... :)


    2) rt error '75' Path/File access error


    3) Same line error as one of my earlier trial tests.


    Code
    [B]Open OutFilePath For Output As FileNum[/B]


    4) And another difference is that the .vcf file stays open. I'm pretty sure macro can write to it later from the last section if it's open.



    I'm not 100% sure this is what you meant. But this is your code I replaced (the original bold area) with...



    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Hello,
    This will increment the number in the file

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Just realised what is going on you are looking for unique vcf file name

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Now I'm getting confused. :)


    I think I still need to create the file and close it...


    Code
    Wb.SaveAs filename:=strFolder & strFile & intUnique & strExt 
Wb.Close '

    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    But, yes, exactly.


    Maybe this?


    Re: How to use InputBox, accepting initial input and avoid the MsgBox.


    Wait, I see where you're going with this! "For Output As FileNum"


    Code
    Open strFolder & strFile & intUnique & strExt [B]For Output As FileNum[/B]


    But I'm sure I need to close the file before the last rewrite section.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login