Re: How to use InputBox, accepting initial input and avoid the MsgBox.
Hi,
no you cant save a xls work book as a cvf file
you want to write from the xls to a cvf file
Re: How to use InputBox, accepting initial input and avoid the MsgBox.
Hi,
no you cant save a xls work book as a cvf file
you want to write from the xls to a cvf file
Re: 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...
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.
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.
Hi,
apologies I missed it in the first code
Which popup and where?
is it an alert ?
you can set the displayalerts to false but I see that in your code already
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
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
Display More
Re: How to use InputBox, accepting initial input and avoid the MsgBox.
This is the InputBox popup I'm trying to avoid.
Re: How to use InputBox, accepting initial input and avoid the MsgBox.
Loop
End If
wb.SaveAs Filename:=strFolder & strFile & intUnique & ".xls" ' cant save xls as vcf
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
Display More
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
I don't know if it's because I'm normally using .CSV but you saw the original working code.
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
Display More
Re: How to use InputBox, accepting initial input and avoid the MsgBox.
Morning,
The way is to increment the file number is to separate it in the routine. The do look will negate the input box
fn1 = Format(Date, "dddmmmdd") & "-" ' & Mid(wb.Worksheets(1).Cells(i, 8), 1, 3)
fn2 = CLng(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
fn3 = pth & "\" & fn1 & fn2 & ".vcf"
'fn1 = pth & "\" & fn1 & fn2 & ".vcf"
'For j = 1 To 62
Do Until Dir$(fn3) = vbNullString
'If Dir(fn1) <> "" Then
increment = increment + 1
fn2 = fn2 + increment
fn3 = pth & "\" & fn1 & fn2 & ".vcf"
' 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
Loop
newfilename = fn3
Display More
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.
Don’t have an account yet? Register yourself now and be a part of our community!