Click here to Skip to main content
15,565,403 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
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

VB
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).


VB
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
Posted
Updated 8-Nov-11 0:01am
v3

1 solution

Did you try:
VB
Worksheets("worksheetname").PrintOut

or
if you wnat see the worksheet before printing:
VB
Worksheets("worksheetname").PrintPreview
 
Share this answer
 
Comments
Dalek Dave 8-Nov-11 10:26am    
Actually I think I have sussed out what the problem was, I will find out later if I am right.
(Also I did change to Selection.PrintOut)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900