Microsoft Office Forums - View Single Post - Invoice number sync from different location?

lbf200n · 04-10-2013, 06:55 PM

I have got this code from Leith Ross, thanks Leith.

It can let all my Excel invoices have synchronized invoice number.

the txt file with invoice number in it is storing in my hard drive.

I have a hosting service that is currently hosting my website. I was trying to upload the invoice number file to the root address of host server and gave it writable to everyone, and change the DefaultPath in the marco to http:\\www.mywebsite.com\InvoiceNum.txt
and hoping my excel invoice could access and synchronize the invoice number from different location and different computer, but it didn't work.

Where am I doing wrong? or this is not so achievable?

Here is the working code on my local computer:

Code:

'Written: October 15, 2009
'Author:  Leith Ross
'Sumamry: Creates a text file using the file path and name specfied or the defaults
'         if the file doesn't exist. This file holds a serial number starting at one
'         and is incremented and saved each time the function is called.


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 = "G:\"
    DefaultName = "InvoiceNum"
    
      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

Sub InvoiceNumberDisplay()
  Sheets(1).Range("J13") = GetSerialNumber()
End Sub