Saving Excel Attachments

JohnnyBravo

Hi

I have an application that downloads attachments from the email dynamically
However the attachments are in excel 97
Access wont import them dynamically because it expects office XP saved attachments
Is there a way to get access to save the file in the correct format before importing it
through access
thanking you in advance

Capt'n Midnight

how many microsoft programmers does it take to change a light bulb ?
none they just redefine Darkness (TM) as a new industry standard..

No idea - have you checked in add remove programs that you have added in all the filters and converters ?

JohnnyBravo

i dont know what filter to use
or what filter i am missing

RicardoSmith

If you have Access XP then I assume you have Excel XP. Can you not just open them in Excel XP, then save them out as Excel XP. You could script your app to do this using VBA.

JohnnyBravo

Can i script it using VB in access
i.e.
Save the attachment from email
Save the saved file again in XP fromat
Then import them to access

RicardoSmith

Yes you can reference the outlook object and the excel object from within the Access VBA. Just like you would using VB.

JohnnyBravo

Good stuff

Do you have any sample code i code i could look at

RicardoSmith

Nothing handy. I've aminly done Word VBA stuff. But theres lots of forums on this kinda stuff.

http://www.experts-exchange.com/ (excellent for getting help)
http://www.visualbasicforum.com/index.php?
http://vbcity.com/forums/forum.asp?fid=11

JohnnyBravo

i have looked up The object library for excel and have come up with this


Public Sub saveSheets()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strOutputFileName
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Documents and Settings\barres1\Desktop\temp")
xlSheet.SaveAs ("C:\Documents and Settings\barres1\Desktop\temp1")

End Sub


How ever it tells me Object Variable or with block not set
on this line


xlSheet.SaveAs ("C:\Documents and Settings\barres1\Desktop\temp1")


DO you know what the error means or how i can fix it

RicardoSmith

You can't save a sheet only a workbook. The following works.

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:\temp.xls")
xlBook.SaveAs ("C:\temp1.xls")

You can record a macro in the excel macro recorder and then look at the code it produces. Then modify it to suit your needs.

stevenmu

It probably means that the open in the line before failed, are you sure the file exists and the path is right ? It's best to try it with a simpler path (no spaces etc) and you might need the files extension aswell
e.g
Set xlBook = xlApp.Workbooks.Open("C:\temp\temp.xls")

You should also use TypeName to check it afterwards

JohnnyBravo

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:\temp.xls")
xlBook.SaveAs ("C:\temp1.xls")


this does exactly what i want
However
It locks the excel tables so you can open them to edit them
how do i release the lock
The second thing is this code prompts me to overwrite the contents of the previous table i dont want this how do i get rid of that



Thanking you in advance

John

RicardoSmith

Originally posted by JohnnyBravo
...
this does exactly what i want
However
It locks the excel tables so you can open them to edit them
how do i release the lock
The second thing is this code prompts me to overwrite the contents of the previous table i dont want this how do i get rid of that


John

I assume you mean can't open them.

I think you have run this a couple of times with out closing the app and the workbook so it still open in memory. Close all instances of Excel that are running from the Windows Task Manger, Processes tab. Excel is a bit sticky, and if you don't clear all references to the excel objects you are using it has a tendency to stay open. So always close your routines as follows


xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub

What table?

JohnnyBravo

Any table

Say i have a table Called test with info in it and i want to overwrite it with a new email attachment
I want it to be over written without being prompted

JohnnyBravo

Also on a side note i need it to save the excel sheet in the newest format but it still saves it in the old one
When you do it manually it prompts you to save it in the new format
Does anyone have any ideas

RicardoSmith

Originally posted by JohnnyBravo
Also on a side note i need it to save the excel sheet in the newest format but it still saves it in the old one
When you do it manually it prompts you to save it in the new format
Does anyone have any ideas

Did you try recording a macro...

ActiveWorkbook.SaveAs Filename:= "C:\Book1.xls", FileFormat:= xlExcel9795
ActiveWorkbook.SaveAs Filename:= "C:\Book1.xls", FileFormat:= xlNormal

I don't know that much about access vba. So Someone else will have to help you with the access vba side of it.

JohnnyBravo

Public Sub saveSheets()
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:\temp.xls")
xlBook.SaveAs "C:\temp1.xls", acSpreadsheetTypeExcel97
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub


This is what i have
It prompts me to save it in a location as in if you were to manually save as
i just want it to save it without all this feed back

Gurgle

OK, I haven't used access xp, but in access 2000, you can make a macro which uses
transferspreadsheet to import an excel spreadsheet in whatever version you select.

You can also set the warnings to overwrite the table without prompting, using setwarnings.

JohnnyBravo

Access XP cannot deal with excel 2.1 spreadsheets and if it can i dont know which
"ACSpreadSheetTypeExcel" to use

I have tried 1-9
and 97 but it still expects a different format
Thus why i want to save it first in the right format before importing it

RicardoSmith

Originally posted by JohnnyBravo
....It prompts me to save it in a location as in if you were to manually save as
i just want it to save it without all this feed back

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strOutputFileName
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\temp.xls")
xlBook.SaveAs ("C:\temp1.xls")

xlBook.Saved = True 'Set to "saved" so that user isn't prompted
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub

RicardoSmith

To be honest importing and exporting stuff via excel isn't very robust. You'd better if you used some other data format like xml or csv etc.

JohnnyBravo

id love to but im stuck with excel

JohnnyBravo

This is excellent
However
I want it to overwrite a spread sheet with out prompting



Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strOutputFileName
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\temp.xls")
xlBook.SaveAs ("C:\temp1.xls")

xlBook.Saved = True 'Set to "saved" so that user isn't prompted
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub

there must be a way to turn it off