Excel - Print to Local Printer from Worksheet on server

See more:

MS-Excel

VBA

I have a spreadsheet held on a server, to which many have access across several locations.

It is part of a form that requires unique numbers for each form.
So on open it increases the number by one (Held in a text file), and for each printed form it increases that number by one.

So far so good, all is well.

However, whilst it works for me, printing to the printer next to me, my brother, (for whom I am doing this), says that it crashes at this line

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"

I would like that who ever opens, at whatever location, the form prints to the local printer at their location.



Here is the Module :
See towards the bottom for the print section.

(And no laughing at my appalling code, it was racked up in front of the TV last night, and I couldn't be bothered to work too hard as it is for my brother, and he ain't paying).

Function GetSerialNumber(Optional FilePath As String, Optional FileName As String) As String

  Dim DefaultName As String
  Dim DefaultPath As String
  Dim FSO As Object
  Dim SeqNum As Variant
  Dim TxtFile As Object
  
    DefaultPath = "C:\Users\Jonathan Costigan\Desktop"
    DefaultName = "Invoice"
    
      FilePath = IIf(FilePath = "", DefaultPath, FilePath)
      FilePath = IIf(Right(FilePath, 1) <> "\", FilePath & "\", FilePath)
    
      FileName = IIf(FileName = "", DefaultName, FileName)
      FileName = FilePath & IIf(InStr(1, FileName, ".") = 0, FileName & ".txt", FileName)
    
      Set FSO = CreateObject("Scripting.FileSystemObject")
    
       'Open the file for Reading and Create the file if it doesn't exists
        Set TxtFile = FSO.OpenTextFile(FileName, 1, True, 0)
      
       'Read the serial number
        If Not TxtFile.AtEndOfStream Then SeqNum = TxtFile.ReadLine
        TxtFile.Close
      
       'Update the serial number
        Set TxtFile = FSO.OpenTextFile(FileName, 2, False, 0)
        SeqNum = Format(IIf(SeqNum = "", "1", Val(SeqNum) + 1), "0000")
        TxtFile.WriteLine SeqNum
      
   TxtFile.Close
   GetSerialNumber = SeqNum
      
   Set FSO = Nothing
   Set TxtFile = Nothing
   
End Function
'--------------------------------------------------------
Function EndSerialNumber(Optional FilePath As String, Optional FileName As String) As String

  Dim DefaultName As String
  Dim DefaultPath As String
  Dim FSO As Object
  Dim SeqNum As Variant
  Dim TxtFile As Object
  
    DefaultPath = "C:\Users\Jonathan Costigan\Desktop"
    DefaultName = "Invoice"
    
      FilePath = IIf(FilePath = "", DefaultPath, FilePath)
      FilePath = IIf(Right(FilePath, 1) <> "\", FilePath & "\", FilePath)
    
      FileName = IIf(FileName = "", DefaultName, FileName)
      FileName = FilePath & IIf(InStr(1, FileName, ".") = 0, FileName & ".txt", FileName)
    
      Set FSO = CreateObject("Scripting.FileSystemObject")
    
       'Open the file for Reading and Create the file if it doesn't exists
        Set TxtFile = FSO.OpenTextFile(FileName, 1, True, 0)
      
       'Read the serial number
        If Not TxtFile.AtEndOfStream Then SeqNum = TxtFile.ReadLine
        TxtFile.Close
      
       'Update the serial number
        Set TxtFile = FSO.OpenTextFile(FileName, 2, False, 0)
        SeqNum = Format(IIf(SeqNum = "", "1", Val(SeqNum) - 1), "0000")
        TxtFile.WriteLine SeqNum
      
   TxtFile.Close
   EndSerialNumber = SeqNum
      
   Set FSO = Nothing
   Set TxtFile = Nothing
   
End Function
'--------------------------------------------------------
Sub Auto_Open()
  Sheets(1).Range("B2") = GetSerialNumber()
End Sub
Sub Auto_Close()
  Sheets(1).Range("B2") = EndSerialNumber()
End Sub
'--------------------------------------------------------
Sub PrintNumberIncrease()
    Dim Turns As Integer
    Turns = InputBox(Prompt:="Number please.", _
          Title:="ENTER NUMBER OF PRINTS", Default:="Number Here")
    For i = 1 To Turns
    Range("A1:G20").Select
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"
    Range("I10").Select
    Sheets(1).Range("B2") = GetSerialNumber()
    Next
End Sub