PDA

View Full Version : Solved: open, save files



satyen
05-05-2011, 02:24 PM
Hello

Can someone please help.

I would like some code that looks in a directory, opens up named files (in code) then saves them so the last accessed date is the current date.


Many thanks for your help.

Kenneth Hobs
05-05-2011, 05:49 PM
That is easily done but if you just want to change the Modification date, that can be done without opening the file. Of course if you don't open it, the file data may not update as you might want.

Record a macro and then edit the filename that it opens, saves, and closes.

satyen
05-07-2011, 06:17 AM
Ive tried this and not having any luck. The code is just recording:


Sub Macro4()
'
' Macro4 Macro

'

'
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

satyen
05-07-2011, 06:22 AM
I have this code which opens all .txt files in a directory, however this is not what I am looking for. I need to specify the actual file names. Any help would be most welcome.


Sub OpenFile()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Dim myFile As String, myPath As String
myPath = "C:\Files\"
myFile = Dir(myPath & "*.txt")
Do
Workbooks.Open myPath & myFile
myFile = Dir()
ActiveWorkbook.Close True
Loop While myFile <> ""
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Kenneth Hobs
05-07-2011, 06:26 AM
Where is your Workbooks.Open? My recording went something like:
Sub Macro1()
'
' Macro1 Macro
'

'
Workbooks.Open Filename:="C:\MyFiles\Excel\Ranges\SplitToRight.xlsm"
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

The code can be simplified to:
Sub Macro1()
Workbooks.Open "C:\MyFiles\Excel\Ranges\SplitToRight.xlsm"
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

satyen
05-07-2011, 06:30 AM
Thank you. There are 6 specific text files I need it to save so that the modified date changes.

Kenneth Hobs
05-07-2011, 06:51 AM
Was there a question in what you last posted?

Assuming so, here is one approach. In the VBE, put this into a Module. Obviously, you need to change the strings in the array a().

Sub Test_OpenSaveXLFiles()
Dim a(1 To 6) As String
a(1) = "x:\t\Book1.xls"
a(2) = "x:\t\Book2.xls"
a(3) = "x:\t\Book3.xls"
a(4) = "x:\t\Book4.xls"
a(5) = "x:\t\Book5.xls"
a(6) = "x:\t\Book6.xls"
OpenSaveXLFiles a()
End Sub

Sub OpenSaveXLFiles(listOfFiles() As String)
Dim i As Variant
For Each i In listOfFiles()
OpenSaveXLFile CStr(i)
Next i
End Sub

Sub OpenSaveXLFile(anXLFilename As String)
If Dir(anXLFilename) = "" Then Exit Sub
Workbooks.Open anXLFilename
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub