Macro Programming MS oficce 2010 Save AS

Hello I have a code that was created by one of the members here, and I am trying to figure out how to add to it.  What this code does is as soon as its opened, it generates a number and puts it to a log file, and this number is used as a invoice number on the cell N2.  But what I also need is also, that as soon as its opened, the file will be saved as an xslx documents so that the user can make any changes to the document and come back to it without the number being touched.  The main template will be stored somewhere and a user can open it up, and I want it to save as to an xslx to another location and close the xlsm file.  THe filename of the new file is going to be "invoice_strNum.

Can anyone help

Private Sub Workbook_Open()
    Const ForReading = 1
    Const ForAppending = 8
    Dim objFSO As Object
    Dim objFil As Object
    Dim strFilePath As String
    Dim strLast As String
    Dim strNum As String
    Dim strContents As String
    Dim arrLines
    strFilePath = ThisWorkbook.Path & "\log.txt"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    Set objFil = objFSO.getfile(strFilePath)
    On Error GoTo 0
    If objFil Is Nothing Then
        Set objFil = objFSO.createTextFile(strFilePath, ForWriting)
        objFil.writeline "1," & Environ("username") & "," & Now()
    Else
        Set objFil = objFSO.OpenTextFile(strFilePath, ForReading)
        strContents = objFil.ReadAll
        objFil.Close
        Set objFil = objFSO.OpenTextFile(strFilePath, ForAppending)
        arrLines = Split(strContents, vbCrLf)
        strLast = arrLines(UBound(arrLines) - 1)
        strNum = Val(Left(strLast, InStr(strLast, ",") - 1)) + 1
        objFil.writeline strNum & "," & Environ("username") & "," & Now()
    End If
    objFil.Close
    Sheets("EXPENSE FORM").Range("N2").Value = strNum
End Sub

Open in new window

falimoradAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SiddharthRoutConnect With a Mentor Commented:
Please incorporate this code in the above code to save the file as .xlsx

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "C:\MyFile.xlsx"
    Application.DisplayAlerts = True

Open in new window


Sid
0
 
falimoradAuthor Commented:
sid can I concatenate the number to the file name, and can I close the existing xslm file.
0
 
SiddharthRoutCommented:
Yes. In that case, put the code in the end after the number is stored in the variable strNum

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "C:\MyFile" & strNum & ".xlsx"
    Application.DisplayAlerts = True

Open in new window


Sid
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
falimoradAuthor Commented:
Hey Sid,

I am getting an error, could be because its an xlsm file
err.jpg
0
 
SiddharthRoutCommented:
What happens when you press debug?

Sid
0
 
falimoradAuthor Commented:
It shows ActiveWorkbook.SaveAs "C:\MyFile" & strNum & ".xlsx" is highlighted, but I actually was googling this and the solution seems to be

ActiveWorkbook.SaveAs "C:\MyFile" & strNum & ".xlsx", FileFormat:=51

I wouldnt have found it if it wasnt for your solution.  

Another quick question, how can I set it so that when the file is opened, it wont give the enable macro option and just enable it.
0
 
SiddharthRoutCommented:
0
 
falimoradAuthor Commented:
SID is the best, whenever I have a problem and I see that he is one of the participants in answering, I know that the solution is going to be quick and precise.
0
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.

All Courses

From novice to tech pro — start learning today.