Solved

Macro Programming MS oficce 2010 Save AS

Posted on 2011-03-01
8
357 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Receiving a string from a WebService Push 21 37
Access Excel export not behaving 2 25
Excel - find text within text? 1 24
remove upper case characters in excel 4 19
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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.

920 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

15 Experts available now in Live!

Get 1:1 Help Now