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

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

I'm just getting how this one works. I don't want to start over.

I think you're on to avoiding the InputBox popup, I'm just not sure on the last steps.

Specifically these lines...

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

And I think I get what you're doing here, just not 100% sure, how it works together and I know I can't leave it open because of the final step.

Open strFolder & strFile & intUnique & strExt For Output As FileNum

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

I think I have it. I'll post in a moment.

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

This is getting anoying! lol

I tried this below, with and commenting out

OutFilePath = strFolder & strFile & intUnique & strExt

And I'm still back to rt error 9 'subscript out of range'! @ Set wk = Worksheets(2)

fwiw I would think this would cause a problem in the original macro since it's defined earlier as Set wk2 = Worksheets(2), but it doesn't. Only now.

Loop
     
End If
 
wb.SaveAs Filename:=strFolder & strFile & intUnique & strExt
wb.Close
Set fs = Nothing
             
    Set wk = Worksheets(2)
         
         iRow = 2
         FileNum = FreeFile
'        OutFilePath = "C:\President Files\Leads\Leads Temp\fulfillment\ACTIVE" & "\OutputVCF.vcf"
'        OutFilePath = strFolder & strFile & intUnique & strExt
         
         Open strFolder & strFile & intUnique & strExt For Output As FileNum
         'Loop through Excel Sheet each row and write it to VCF File

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

This is the InputBox popup I'm trying to avoid.

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)

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

Same rt error 9 'subscript out of range' @ Set wk = Worksheets(2)

Adding this code back in is the same too. I know I need this reference later either way. OutFilePath

OutFilePath = strFolder & strFile & intUnique & strExt

I don't know if it's because I'm normally using .CSV but you saw the original working code.

fn1 = pth & "\" & fn1 & ".vcf"

It definitely has to save as a .vcf not an .xls in the code above.

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

I don't know where that idea came from in the first place.

I just tested our original code creating the vcf file from an .xls file rather than a .csv, and it worked perfectly.

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

fn1 = Format(Date, "dddmmmdd") & "-" & Mid(wb.Worksheets(1).Cells(i, 8), 1, 3)
         
    pth = "C:\President Files\Leads\Leads Temp\fulfillment\ACTIVE" ' You can change path just like as too
    
    fn1 = pth & "\" & fn1 & ".vcf"
    For j = 1 To 62
        If Dir(fn1) <> "" Then
            fn2 = Format(Date, "dddmmmdd") & "-" & CLng(Mid(wb.Worksheets(1).Cells(i, 8), 1, 3)) + j
            If Dir(pth & "\" & fn2 & ".vcf") = "" Then
                 fn1 = pth & "\" & fn2 & ".vcf"
            End If
        End If
    Next j

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

Thanks for your help! The code I added seems to work exactly as we need with the automatic filename increment.