Solved

Macro Programming MS oficce 2010 Save AS

Posted on 2011-03-01
8
356 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:falimorad
  • 4
  • 4
8 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35011576
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
 

Author Comment

by:falimorad
ID: 35011627
sid can I concatenate the number to the file name, and can I close the existing xslm file.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011639
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
 

Author Comment

by:falimorad
ID: 35011675
Hey Sid,

I am getting an error, could be because its an xlsm file
err.jpg
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011727
What happens when you press debug?

Sid
0
 

Author Comment

by:falimorad
ID: 35011753
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011782
0
 

Author Closing Comment

by:falimorad
ID: 35011825
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now