Excel and using a counter

When the user hits export the application exports all the date on a certain sheet to a word template then saves it as a number in Cell D of that worksheet.  

Well that number is still being used but the filenames need to be saved as a different number as we are using a new template.
I added a text box on the form that allows the user to enter a starting number, how do I get it so I can use that number and every number after that until it has finished outputting all the data.

See snippet for how it works now.

Thanks
John

Private Sub CMD_RUN_IOC_CNE_Click()

MyDate1 = Me.Start_Date.Value
MyDate2 = Me.End_Date.Value

Set ws = ThisWorkbook.Sheets("CNE")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

xMsg1 = MsgBox("Are you sure you want to Continue, this could take longer than five (5) minutes??", vbYesNo, "Proceed with Export?")
        If xMsg1 = vbYes Then
            GoTo MoveOn
        Else
            GoTo Graceful_Cancel
        End If
MoveOn:

For i = 2 To LastRow
Set objWord = CreateObject("Word.Application")

'objWord.Visible = True

Application.StatusBar = "Update Word From Excel: Initialization..."

objWord.Documents.Open IOCS

With objWord.ActiveDocument
.Bookmarks("Title_CO").Range.Text = ws.Range("D" & i).Value             'Bookmark 1
.Bookmarks("Subject").Range.Text = ws.Range("D" & i).Value              'Bookmark 2
.Bookmarks("From").Range.Text = ws.Range("I" & i).Value                 'Bookmark 3
.Bookmarks("CO_Num").Range.Text = ws.Range("D" & i).Value               'Bookmark 4
.Bookmarks("CO_Title").Range.Text = ws.Range("F" & i).Value             'Bookmark 5
.Bookmarks("Verification_Method").Range.Text = ws.Range("H" & i).Value  'Bookmark 6
.Bookmarks("CO_Objective").Range.Text = ws.Range("D" & i).Value         'Bookmark 7
.Bookmarks("Doors_ID").Range.Text = ws.Range("C" & i).Value             'Bookmark 8
.Bookmarks("Source_Objective").Range.Text = ws.Range("G" & i).Value     'Bookmark 9
.Bookmarks("SC_1").Range.Text = ws.Range("J" & i).Value                 'Bookmark 10
.Bookmarks("SC_2").Range.Text = ws.Range("K" & i).Value                 'Bookmark 11
.Bookmarks("SC_3").Range.Text = ws.Range("L" & i).Value                 'Bookmark 12
.Bookmarks("SC_1_DR_1").Range.Text = ws.Range("P" & i).Value            'Bookmark 13
.Bookmarks("SC_2_DR_2").Range.Text = ws.Range("Q" & i).Value            'Bookmark 14
'.Bookmarks("SC_3_DR_3").Range.Text = ws.Range("R" & i).Value            'Bookmark 15; Not currently Used
.Bookmarks("CO_NO").Range.Text = ws.Range("D" & i).Value                'Bookmark 16
.Bookmarks("Footer").Range.Text = ws.Range("D" & i).Value               'Bookmark 17
.Bookmarks("Footer_1").Range.Text = ws.Range("D" & i).Value             'Bookmark 18
.Bookmarks("Start_Date").Range.Text = IOC_Form.Start_Date.Value         'Bookmark 19
.Bookmarks("End_Date").Range.Text = IOC_Form.End_Date.Value             'Bookmark 20


strPath = Me.IOC_CNE_Path & "\" & ws.Range("D" & i).Value & ".docx"

objWord.ActiveDocument.SaveAs FileName:=strPath, _
AddToRecentFiles:=False
objWord.Quit False

End With

Next i

gracefulExit:
    Application.StatusBar = False
    'objWord.Quit True
    Set objWord = Nothing
    xMsg2 = MsgBox("Export Completed Successfully", vbOKOnly, "SUCCESS!!!")
Exit Sub

Graceful_Cancel:
    Application.StatusBar = False
    'objWord.Quit True
    Set objWord = Nothing
    xMsg2 = MsgBox("Export Canceled", vbOKOnly, "CANCELED!!!")
    

End Sub

Open in new window

John SheehySecurity AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
Not totally sure what you mean, but I'm guessing you want a global variable ? Declare it at the top of your code. Totally on top, outside your subs.
0
John SheehySecurity AnalystAuthor Commented:
What I need is a sequential number.  

On the text box of the form the user enters 12001 and the application uses that number and every number after that until it is done outputting all the records as the filename of the word documents.

Right now it does it like this:

strPath = Me.IOC_CNE_Path & "\" & ws.Range("D" & i).Value & ".docx"

It uses the number in Cell D as the filename with the docx extension.

I want it to use the number in text box on the form and be sequential from that number on till it is done outputting the records on the form.

John
0
John SheehySecurity AnalystAuthor Commented:
Here is the code for the whole application right now

Dim objWord As Object
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim FileName As String, strPath As String
Dim xMsg1, xMsg2, xMsg3 As Long
Dim IOCS As String
Dim MyDate1, MyDate2 As String

Private Sub CMD_Close_Click()
IOC_Form.Hide
End Sub

Private Sub CMD_IOC_CNE_Path_Click()
Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
 With myFolder
 .Title = "CHOOSE THE FOLDER WHERE THE CNE IOC'S WILL BE STORED"
 .AllowMultiSelect = False
 If .Show <> -1 Then
 Exit Sub
 End If
 FolderSelected = .SelectedItems(1)
 End With

Me.IOC_CNE_Path = FolderSelected
End Sub

Private Sub CMD_IOC_LHC_Path_Click()
Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
 With myFolder
 .Title = "CHOOSE THE FOLDER WHERE THE LHC IOC'S WILL BE STORED"
 .AllowMultiSelect = False
 If .Show <> -1 Then
 Exit Sub
 End If
 FolderSelected = .SelectedItems(1)
 End With

Me.IOC_LHC_Path = FolderSelected

End Sub

Private Sub CMD_IOC_LHCSN_Path_Click()
Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
 With myFolder
 .Title = "CHOOSE THE FOLDER WHERE THE LHCSN IOC'S WILL BE STORED"
 .AllowMultiSelect = False
 If .Show <> -1 Then
 Exit Sub
 End If
 FolderSelected = .SelectedItems(1)
 End With

Me.IOC_LHCSN_Path = FolderSelected
End Sub

Private Sub CMD_IOC_LHCT_Path_Click()
Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
 With myFolder
 .Title = "CHOOSE THE FOLDER WHERE THE LHCT IOC'S WILL BE STORED"
 .AllowMultiSelect = False
 If .Show <> -1 Then
 Exit Sub
 End If
 FolderSelected = .SelectedItems(1)
 End With

Me.IOC_LHCT_Path = FolderSelected
End Sub

Private Sub CMD_IOC_LSM_Path_Click()
Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
 With myFolder
 .Title = "CHOOSE THE FOLDER WHERE THE LSM IOC'S WILL BE STORED"
 .AllowMultiSelect = False
 If .Show <> -1 Then
 Exit Sub
 End If
 FolderSelected = .SelectedItems(1)
 End With

Me.IOC_LSM_Path = FolderSelected
End Sub

Private Sub CMD_IOC_Path_Click()

Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
 With myFolder
 .Title = "CHOOSE THE FOLDER WHERE THE SAMPLE_IOC.DOCX FILE TEMPLATE IS STORED"
 .AllowMultiSelect = False
 If .Show <> -1 Then
 Exit Sub
 End If
 FolderSelected = .SelectedItems(1)
 End With

Me.IOC_Path = FolderSelected & "\Sample_IOC.docx"
IOCS = Me.IOC_Path

'Me.Start_Date.Value = Format(Date, "MM/DD/YYYY")
'Me.End_Date.Value = Format(Date, "MM/DD/YYYY")

End Sub

Private Sub CMD_IOC_PIDS_Path_Click()
Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
 With myFolder
 .Title = "CHOOSE THE FOLDER WHERE THE PIDS IOC'S WILL BE STORED"
 .AllowMultiSelect = False
 If .Show <> -1 Then
 Exit Sub
 End If
 FolderSelected = .SelectedItems(1)
 End With

Me.IOC_PIDS_Path = FolderSelected
End Sub

Private Sub CMD_RUN_IOC_CNE_Click()

MyDate1 = Me.Start_Date.Value
MyDate2 = Me.End_Date.Value

Set ws = ThisWorkbook.Sheets("CNE")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

xMsg1 = MsgBox("Are you sure you want to Continue, this could take longer than five (5) minutes??", vbYesNo, "Proceed with Export?")
        If xMsg1 = vbYes Then
            GoTo MoveOn
        Else
            GoTo Graceful_Cancel
        End If
MoveOn:

For i = 2 To LastRow
Set objWord = CreateObject("Word.Application")

'objWord.Visible = True

Application.StatusBar = "Update Word From Excel: Initialization..."

objWord.Documents.Open IOCS

With objWord.ActiveDocument
.Bookmarks("Title_CO").Range.Text = ws.Range("D" & i).Value             'Bookmark 1
.Bookmarks("Subject").Range.Text = ws.Range("D" & i).Value              'Bookmark 2
.Bookmarks("From").Range.Text = ws.Range("I" & i).Value                 'Bookmark 3
.Bookmarks("CO_Num").Range.Text = ws.Range("D" & i).Value               'Bookmark 4
.Bookmarks("CO_Title").Range.Text = ws.Range("F" & i).Value             'Bookmark 5
.Bookmarks("Verification_Method").Range.Text = ws.Range("H" & i).Value  'Bookmark 6
.Bookmarks("CO_Objective").Range.Text = ws.Range("D" & i).Value         'Bookmark 7
.Bookmarks("Doors_ID").Range.Text = ws.Range("C" & i).Value             'Bookmark 8
.Bookmarks("Source_Objective").Range.Text = ws.Range("G" & i).Value     'Bookmark 9
.Bookmarks("SC_1").Range.Text = ws.Range("J" & i).Value                 'Bookmark 10
.Bookmarks("SC_2").Range.Text = ws.Range("K" & i).Value                 'Bookmark 11
.Bookmarks("SC_3").Range.Text = ws.Range("L" & i).Value                 'Bookmark 12
.Bookmarks("SC_1_DR_1").Range.Text = ws.Range("P" & i).Value            'Bookmark 13
.Bookmarks("SC_2_DR_2").Range.Text = ws.Range("Q" & i).Value            'Bookmark 14
'.Bookmarks("SC_3_DR_3").Range.Text = ws.Range("R" & i).Value            'Bookmark 15; Not currently Used
.Bookmarks("CO_NO").Range.Text = ws.Range("D" & i).Value                'Bookmark 16
.Bookmarks("Footer").Range.Text = ws.Range("D" & i).Value               'Bookmark 17
.Bookmarks("Footer_1").Range.Text = ws.Range("D" & i).Value             'Bookmark 18
.Bookmarks("Start_Date").Range.Text = IOC_Form.Start_Date.Value         'Bookmark 19
.Bookmarks("End_Date").Range.Text = IOC_Form.End_Date.Value             'Bookmark 20


strPath = Me.IOC_CNE_Path & "\" & ws.Range("D" & i).Value & ".docx"

objWord.ActiveDocument.SaveAs FileName:=strPath, _
AddToRecentFiles:=False
objWord.Quit False

End With

Next i

gracefulExit:
    Application.StatusBar = False
    'objWord.Quit True
    Set objWord = Nothing
    xMsg2 = MsgBox("Export Completed Successfully", vbOKOnly, "SUCCESS!!!")
Exit Sub

Graceful_Cancel:
    Application.StatusBar = False
    'objWord.Quit True
    Set objWord = Nothing
    xMsg2 = MsgBox("Export Canceled", vbOKOnly, "CANCELED!!!")
    

End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KimputerCommented:
Store the text box number in a global variable after enter/click/save/hide form (whatever you think is necessary).
Then use that variable until done (maybe reset to 0 to signify it's empty)
0
John SheehySecurity AnalystAuthor Commented:
I did that just a minute ago.

This is what I added:

Dim MyStartingNumber as String

Then on the portion where the code is saving files

MyStartingNumber = Me.Starting_Number

How do I make it sequential from that point on?
0
KimputerCommented:
You can just add to it:

Dim MyStartingNumber As String

MyStartingNumber = "1"

MyStartingNumber = MyStartingNumber + 1

MsgBox MyStartingNumber

Open in new window


It's not nice (as you start with a string but treat it like it's an Integer), but it works. You could also choose to start it really as an Integer, and then read the text, and use CINT and IsNumeric and what not to include some error handling.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SheehySecurity AnalystAuthor Commented:
So I changed it to:
DIM MyStartNumber as Integer

can I still use:
MyStartingNumber = MyStartingNumber + 1

And how does it know to do sequential if it keeps adding one number to the number on the form.
Meaning if the number on the form is 12001 and it adds 1 then it becomes 12002 but does it change the number on the form to 12002?  Because if it stays 12001 then all it will keep on doing is adding 1 to that number and I will have duplicate files all over the place.

John
0
KimputerCommented:
Update the text in the form also after you add 1.
0
John SheehySecurity AnalystAuthor Commented:
All though I seemed to of made it seem complex your answer was rather simple.  And it works like a charm.  Thank you very much.

I posted a question out there about skipping lines in excel.  If you want to take a stab at that one.

Thanks
John
0
John SheehySecurity AnalystAuthor Commented:
Solution was fast and it worked out perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.