Prev: Automatically Update a Pivot Table Field Used as Column / Row
Next: Formula from closed workbook?
From: Ron de Bruin on 13 Apr 2010 14:26
Do you say that the macro example from my site is not working if you run it in 2003


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



"Keith G Hicks" <krh(a)comcast.net> wrote in message news:#lBRs5n2KHA.1708(a)TK2MSFTNGP05.phx.gbl...
> That's where I got some of the code I'm using but it only works if you are
> running it in 2007/2010. If you try to do this:
>
> objExcelBk.SaveAs sTempPath & sExcelFileName, 56
>
> in Access 2003 vba, it doesn't run because "56" is not a valid version # as
> far as vba 2003 is concerned.
>
>
> "Ron de Bruin" <rondebruin(a)kabelfoon.nl> wrote in message
> news:%23kNuhtn2KHA.3844(a)TK2MSFTNGP05.phx.gbl...
>> See
>> http://www.rondebruin.nl/saveas.htm
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>>
>> "Keith G Hicks" <krh(a)comcast.net> wrote in message
>> news:eCw7e$k2KHA.4016(a)TK2MSFTNGP05.phx.gbl...
>>> I think this will solve the problem. Any feedback?
>>>
>>> Dim lCurrXlVersion As Long
>>> lCurrXlVersion = objExcelApp.Version
>>>
>>> objExcelApp.DisplayAlerts = False
>>> Select Case lCurrXlVersion
>>> Case 11
>>> objExcelBk.SaveAs sTempPath & sExcelFileName
>>> Case 12, 14
>>> objExcelBk.SaveAs sTempPath & sExcelFileName, 56 '56 = xl
>>> 2000/2003
>>> Case Else
>>> MsgBox "Error saving Excel file because you are not using
>>> version 2003, 2007 or 2010"
>>> End Select
>>>
>>>
>>>
>>> "Keith G Hicks" <krh(a)comcast.net> wrote in message
>>> news:uWW6M4k2KHA.4016(a)TK2MSFTNGP05.phx.gbl...
>>>> Well to make this worse, I just learned that 2007 does not support
>>>> saving as 95/97 so I'm guessing that if someone using Office 2007 runs
>>>> my code that has xlExcel9795 in it then they will get an error too.
>>>>
>>>>
>>>> "Keith G Hicks" <krh(a)comcast.net> wrote in message
>>>> news:ujp%23b1k2KHA.4336(a)TK2MSFTNGP04.phx.gbl...
>>>>> I'm working in vba in Access to create and save an Excel file. All's
>>>>> good except that one of the workstations this is runnign on is using
>>>>> Office 2007. I'm developing in 2003 and all the other workstatiosn they
>>>>> have are using 2003. It's very important that the files be saved in
>>>>> 2003 format.
>>>>>
>>>>> When I do this, it runs fine and saves as 97/95
>>>>>
>>>>> objExcelBk.SaveAs sTempPath & sExcelFileName, xlExcel9795 ','56 = xl
>>>>> 2000/2003
>>>>>
>>>>> I read online in a forum post that "56" is the correct code for saving
>>>>> as 2003 but that's when the code is written in 2007. I cannot find a
>>>>> constant in the list of constants in 2003 to save as 2003 (like
>>>>> xlExcel0003 for example) and the "56" below causes the code to crash.
>>>>>
>>>>> objExcelBk.SaveAs sTempPath & sExcelFileName, 56
>>>>>
>>>>> I may end up having to use xlExcel9795 and I think that will be okay
>>>>> but how do I code this to ensure saving as 2003 format while writing
>>>>> the code in 2003?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Keith
>>>>>
>>>>
>>>>
>>>
>
First  |  Prev  | 
Pages: 1 2
Prev: Automatically Update a Pivot Table Field Used as Column / Row
Next: Formula from closed workbook?