PDA

View Full Version : Solved: Get the DateModified of the import file in a cell



Riaaz66
10-21-2010, 12:16 AM
Hi,

I'm using the vba-code below, to import a worksheet from another excel file to my workbook.
From the file that's being imported, I want to have the 'date created' into a cell of my workbook. Is that possible?

Here's the code I use to import an excel file:

Sub Get_Exact_Data()
'Original VBA code name "OpenExcelFile"

Dim vFile As Variant
Application.ScreenUpdating = False

Sheets("Exact Data").Select
Range("A1:Z10000").Select
Selection.Clear
Application.ScreenUpdating = True


'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("XLS Files (*.xls)," & _
"*.xls", 1, "Select Excel File", "Open", False)
'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
End If
'Empty clipboard
Application.CutCopyMode = False

'Open selected file
Workbooks.Open vFile

ChDir _
"H:\Exactdat\"
'ActiveWorkbook.SaveAs Filename:="H:\Exactdat\Exact_Recon_Test.xls" _
', FileFormat:=xlXLSSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False


Columns("A:L").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-10000
Windows("G1 vs Exact Recon Tool - v1.xls").Activate
Sheets("Exact Data").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Windows("Exact_Recon_Test.xls").Activate
ActiveWorkbook.Close
Windows("G1 vs Exact Recon Tool - v1.xls").Activate
Sheets("Exact Data").Select
Range("A1").Select

Sheets("Main").Select
End Sub

Regards,

RM

Bob Phillips
10-21-2010, 12:38 AM
Try



Range("A1").Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date")

Riaaz66
10-21-2010, 04:31 AM
Hi xld,

Where should I put the provided code in to my code?

Regards,

RM

Kenneth Hobs
10-21-2010, 05:37 AM
We can't read your mind. You have to tell us something other than put it in a cell. A1 is a cell. Try explaining what you need in generic terms. e.g. After pasting, add date value to the last row with data in column B and set the format to dd/mm/yyyyy.

Riaaz66
10-21-2010, 06:21 AM
Ok Mr. Hobs, fair enough...I should've know that.

Here is what I want:
- I have workbook TEST.xls with 4 tabs.
- Tab 1 needs to retreive data from another workbook. e.g. DATA1.xls
For this action, I use the early posted VBA code.
- When the data is retreived in TEST.xls sheet1, I want to have the CreateDate of DATA1.xls in workbook TEST.xls, Sheet1, cell M1

Can you help me?

Regards,

RM

Kenneth Hobs
10-21-2010, 03:41 PM
With both workbooks open, untested:
WorkBooks("Test1.xls").Worksheets("Sheet1").Range("M1").Value = WorkBooks("Data1").BuiltinDocumentProperties("Creation Date")