why cant I open this file?

[smiler44]

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

 

[HaHoBe]

Hi, smiler44,

why don´t you use the FileFormat for SaveAs as described in Use VBA SaveAs in Excel 2007? 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

 

[smiler44]

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.

smiler44