Sequential Numbering Macro

I have a macro for word as per the following. My question is, does anyone know how I would implement this into my excel file to use the same numbering system as the word files. Would a different Macro be needed for excel than the below or is it even possible? THANKS SO MUCH IN ANTICIPATION!

Sub AutoRef()

Order = System.PrivateProfileString("T:\Storage_Area\M\MacroSettings\Settings.txt", _
        "MacroSettings", "Order")

If Order = "" Then
    Order = 1
Else
    Order = Order + 1
End If

System.PrivateProfileString("T:\Storage_Area\M\MacroSettings\Settings.txt", "MacroSettings", _
        "Order") = Order

ActiveDocument.Bookmarks("Order").Range.InsertBefore Format(Order, "00#")
ActiveDocument.SaveAs FileName:="T:\Storage_Area\NEW_DOCS\SDL-LET-" & Format(Order, "00#")

End Sub

Hello Stedia,

You can use the same macro by adding a reference to the Word object library in your project.
Setting a Reference in VBA

1. Open your workbook and then press ALT+F11 to open the VBE.
2. Press ALT+T followed by the Enter key.
3. Scroll down the list until you find Microsoft Word x.x Object Library.
4. Press the Space bar to select it, and press Enter
5. Press CTRL+S to save the reference in the project

Sincerely,
Leith Ross

Hi I did exactly as you said. When I run the macro I get a syntax error. I am trying to put an auto reference in cell A16 when I run the macro. Could anyone run me through any additional process that I need to do so that when I run the macro it enters the number in cell A16. Maybe I need to change the macro slightly??? I am a total novice. You said that if I did what you said, it would work but have I missed something that maybe you thought I would have already known?

Many many thanks for your reply.

STEDIA

Hello Stedia,

I made a few changes to the macro code. It place the order number in cell A16 of the active sheet. The reference must still be made to the Word Object Library. Double check the file names to make sure they are correct.

Sub AutoRef()

Order = System.PrivateProfileString("T:\Storage_Area\M\MacroSettings\Settings.txt", _
        "MacroSettings", "Order")

If Order = "" Then
    Order = 1
Else
    Order = Order + 1
End If

Excel.ActiveSheet.Range("A16").Value = Order

System.PrivateProfileString("T:\Storage_Area\M\MacroSettings\Settings.txt", "MacroSettings", _
        "Order") = Order

Excel.ActiveWorkbook.SaveAs FileName:="T:\Storage_Area\NEW_DOCS\SDL-LET-" & Format(Order, "00#")

End Sub

Sincerely,
Leith Ross

Hi. Did that. Made sure the paths/filenames were correct. Now getting the following:

Run-time error'429':

ActiveX component can't create object.

Any ideas? Thanks for all your help this far. :-)

STEDIA

Hello Stedia,

This macro creates the file if it doesn't already exist, increments the Order Number, and copies it to Range("A16") on the Active Sheet. Turns out you do need to create an instance of the Word Application for this to run. Sorry for confusion, I thought youhad Word running along with the Excel program.

Test Macro Code

Sub GetOrderNumber()
  
 'Requires a reference to the Word x.x Object Library
 
  Dim Order As Variant
  Dim TxtFile As String
  Dim WD As Object
  
    Set WD = CreateObject("Word.Application")
    TxtFile = "C:\Order Number Test.txt"

    Order = WD.System.PrivateProfileString(TxtFile, "Order Number", "Last Order")
      If Order = "" Then
         Order = 1
      Else
         Order = Order + 1
      End If
  
    ActiveSheet.Range("A16").Value = Order
    WD.System.PrivateProfileString(TxtFile, "Order Number", "Last Order") = Order
  
    Set WD = Nothing
    
End Sub

Your Updated Macro

Sub GetOrderNumber()
  
 'Requires a referencce to the Word x.x Object Library
 
  Dim Order As Variant
  Dim TxtFile As String
  Dim WD As Object
  
    Set WD = CreateObject("Word.Application")
    TxtFile = "T:\Storage_Area\M\MacroSettings\Settings.txt"
  
    Order = WD.System.PrivateProfileString(TxtFile, "MacroSettings", "Order")
      If Order = "" Then
         Order = 1
      Else
         Order = Order + 1
      End If
  
    ActiveSheet.Range("A16").Value = Order
    WD.System.PrivateProfileString(TxtFile, "MacroSettings", "Order")) = Order
    ActiveWorkbook.SaveAs FileName:="T:\Storage_Area\NEW_DOCS\SDL-LET-" & Format(Order, "00#") 

    Set WD = Nothing
    
End Sub

Sincerely,
Leith Ross