Need Big Help

JohnnyBravo

I have an application that imports attachments to the c drive from the email
On the c drive they are then saved to a different .xcl file so as to give it the most up to date version

They are then imported into Access Here is the code

//Checks to see if the file exists if it does it is then deleted
If Del.FileExists("c:\week1.xls") Then
DeleteAFile ("c:\week1.xls")
End If

If Del.FileExists("c:\week1_Import.xls") Then
DeleteAFile ("c:\week1_Import.xls")
End If

//checks to see if the mail exists if it does import it to the c drive
CheckMailExists
//Saves the sheets to the latest excel format
saveSheets1

//imports them into access
If FileExists("C:\week1.xls") = True Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "week1", "C:\week1_Import.xls", True

Function FileExists(FileName As String)
'This procedure scans the pc for a file
Dim fs As Scripting.FileSystemObject
Set fs = New FileSystemObject
FileExists = fs.FileExists(FileName)
End Function

Function CheckMailExists()
Dim myOlApp As Outlook.Application
Dim myOlFolder As Outlook.MAPIFolder
Dim myItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Dim myNameSpace As Outlook.NameSpace

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myOlFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myItem = myOlFolder.Items.Find("[subject] = ""week1""")
If Not TypeName(myItem) = "Nothing" Then
Set myAttachments = myItem.Attachments
If myAttachments.count > 0 Then
myAttachments.Item(1).SaveAsFile "C:\week1.xls"

End If
End If
End Function

Public Sub saveSheets1()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strOutputFileName As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\week1.xls")
xlBook.SaveAs "C:\week1_Import.xls", acSpreadsheetTypeExcel97
xlBook.Saved = True 'Set to "saved" so that user isn't prompted
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub




Sub DeleteAFile(filespec)
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
fso.DeleteFile (filespec)
End Sub


How ever the problem is when you try import them into access
It Crashes and says it is not of the expected version or something to that effect

I was wondering if ye would look at the code and come up with some suggestions on how to fix this problem


THanking you in advance
John

Morpheus

//imports them into access
If FileExists("C:\week1.xls") = True Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "week1", "C:\week1_Import.xls", True

Are you using Excel 97 on the machine? see above this line of code creates a spreadsheet of type Excel97, you may have a different version of Excel installed on the machine that you import it to???

Just an idea worth, checking it out.

JohnnyBravo

That is the problem
But the excel attachments are in excel 1.1
I open them and save them in office professional XP
so i want to know what that extension could be

ressem

What's Excel 1.1?

According to MSDN the
acSpreadsheetTypeExcel Type constants go from acSpreadsheetTypeExcel3 to
acSpreadsheetTypeExcel9 (probably a 10 now, aka 2003)

The extensions for all Excel files are the same I think. The version is read from the first few bytes of the excel file.

Try 9 (the default, same as 8)?

Edit note:
According to
http://www.tek-tips.com/gpviewthread.cfm/qid/629860/pid/705/lev2/4/lev3/27
you might want to ensure that the first Cell contains data.

JohnnyBravo

I was wondering if you would use the code above to create your own project
Because i have tried all the versions of the AcSpreadsheetTypeExcel and is doesnt work