I have a file saved using code as contractor_spares_sep09.xlsx. It is a .xls file when I first open it. when I try to open the xlsx file using Excel 2007 I get an error message saying "excel can not open the file 'contractor_spares_sep09.xlsx' because the file format or file extension is not valid. verify that the file has not been corrupted and that the file extension matches the format of the file."

There are no macros in contractor_spares so xlsx is right I think. I assume my code is correct but I have put it below anyway.

is it because yyrr is declared as a string and it should be something else?


Dim flle as string

Sub savecopieas()

Dim directoryfound As String
Const errpathnotfound As Integer = 76
Dim handleErr
Dim pat As String ' my documents path
Dim dirr As String ' main directory
Dim yyrr As String 'year
Dim flee As String 'month
pat = SpecFolder(CSIDL_PERSONAL)
dirr = "\AD PMO\garage - updated\book_data\" ' main directory
yyrr = Format(Now, "yy")
flee = "_" & MonthName(Month(Date) - 1, [true]) & yyrr & ".xlsx" ' without macros
strpathname = pat & dirr
Call GetFileName
On Error GoTo 0

directoryfound = Dir(strpathname & flle & flee, vbDirectory)
If (Len(directoryfound)) = 0 Then
ActiveWorkbook.SaveCopyAs FileName:=pat & dirr & flle & flee
Else
Dim x
x = MsgBox(fle & " " & "already exists. Do you wish to over write it?", 36, "confirm")

If x = 6 Then ' yes
ActiveWorkbook.SaveCopyAs FileName:=pat & dirr & subdirr & flle & fle1
Else
MsgBox ("File not saved")
End If
End If
0
End Sub


Sub GetFileName()
'gets just filename without path or file extn
Dim BackSlash As Integer, Point As Integer
Dim FilePath As String, FileName As String
Dim i As Integer

FilePath = ActiveWorkbook.FullName
For i = Len(FilePath) To 1 Step -1
If Mid$(FilePath, i, 1) = "." Then
Point = i
Exit For
End If
Next i
If Point = 0 Then Point = Len(FilePath) + 1
For i = Point - 1 To 1 Step -1
If Mid$(FilePath, i, 1) = "\" Then
BackSlash = i
Exit For
End If
Next i
flle = Mid$(FilePath, BackSlash + 1, Point - BackSlash - 1)
End Sub



smiler44

Hi, smiler44,

why don´t you use the FileFormat for SaveAs as described in Use VBA SaveAs in Excel 2007 (http://www.rondebruin.nl/saveas.htm)? Maybe the standard saving isn´t *.xlsx but *.xls.

Now gives both Date and Time but for getting out the year Date alone should do well.

And for getting the Name of a workbook you could make good use of ThisWorkbook.Name as well as of couting the points inside that name (should not be more than 1) and use InStr as well as Left for the name alone.

Ciao,
Holger

Ciao,
you were right about the file being .xls. Changing the code to save it as xls instead of xlsx has worked, thank you.
I'll look at using Now and Thisworkbook.name but for now I'm chuffed that the project works. The code is not all mine some came form searches on Google while other searches put me on the right track to eventually work things out for myself and of course your tip.

smiler44http://www.access-programmers.co.uk/forums/images/icons/icon7.gif