?
Solved

Save file with creation date

Posted on 2013-02-03
7
Medium Priority
?
365 Views
Last Modified: 2013-02-03
Hello,

I've added row 23-27 to my code to save the active sheet with the created date and time  stamp from the file that was used to import sheet one. It's bugging out on row 27. The file will save if I "Dim asof As Long" but the result is not what I require.

Sub ImportSheet()

        Dim fileName
        Dim wb As Workbook
        Dim strPath As String
        Dim fs, f
        Dim asof As String
        

        fileName = Application.GetOpenFilename("Other Workbook (*.xl*),*.xl*")
        If fileName = "False" Then
        MsgBox "You have not selected a file. Please try again."
        GoTo QuitSub
        End If
        Set wb = Workbooks.Open(fileName:=fileName)
        With ThisWorkbook
            wb.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)
            wb.Close False
            .Activate
            .Worksheets(.Worksheets.Count).Select
        End With
        
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFile(fileName)
        asof = f.DateCreated
        strPath = ThisWorkbook.Path
        ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & asof
        
        
QuitSub:
End Sub

Open in new window

0
Comment
Question by:sq30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38849435
What do you get and what do you want?

You could try changing asof to Date, or if you want today's date just do

ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & Now()
0
 

Author Comment

by:sq30
ID: 38849440
I want the creation date and time stamp of the file I used to import data from.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38849460
And what do you get instead?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:sq30
ID: 38849467
An error have you read the question?
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38849471
sq30,

Replace the "SaveAs" line by...
ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & WorksheetFunction.Substitute(WorksheetFunction.Substitute(asof, "/", "-"), ":", "-")

Brian.
0
 

Author Closing Comment

by:sq30
ID: 38849474
Thank you Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38849477
Thanks, sq30.

Better is...
ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & Replace(Replace(asof, "/", "-"), ":", "-")

Brian.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 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