PDA

View Full Version : Excel 2003 VBA help



hybmg57
04-26-2010, 01:05 AM
Hi, please find below, I am getting "Run-time error '1004': Cannot access 'test 8 Test Company 041110.xls' at ThisWorkbook.SaveAs Filename:="\\hivol\sales\_STAT Sheets\" & strFilename & ".xls", AddToMru:=True

We are using Company Server hivol to store data and the file is saved as 'test 8 Test Company 041110.xls'

This error is coming on a computer with Windows XP and it's Excel 2003.

The one I've supplied is blank... But you can fill required cells in to validate VBA...

I have tried this on Windows 7 and Excel 2007 and strangley it's working fine.

Any ideas? Please help!!!

p45cal
04-26-2010, 03:33 AM
I'd like to bet it's because of illegal characters in the filename.
When the error occurs, have a look at what it's trying to save by typing:
?"\\hivol\sales\_STAT Sheets\" & strFilename & ".xls" in the immediate pane. Does it look right?
It's likely the illegal character is in strFilename so type:
?strFilename to see what it is.

Illegal characters in the filename itself include (may not be a complete list):
< > : " / \ | ? *
but if you find any of the following it might balk too:
CON, PRN, AUX, NUL, COM1, COM2, COM3, COM4, COM5, COM6, COM7, COM8, COM9, LPT1, LPT2, LPT3, LPT4, LPT5, LPT6, LPT7, LPT8, and LPT9

Working backwards, strFilename is created with the line:
strFilename = strControlNo & " " & strCompany & " " & Format$(Range("rngDateOrdered").Value, "MMDDYY")
and strControlNo is created with:
strControlNo = TrimPeriods(Range("rngControlNo").Value)
and strCompany is made with:
strCompany = TrimPeriods(Range("rngCompany").Value)
So any illegal character in one of the named ranges:
rngDateOrdered, rngControlNo or rngCompany
could cause the problem.
You have a TrimPeriods function to strip periods from a string; you could further develop this to remove other illegal characters or add a new function to do it.
Btw. The TrimPeriods function's code can be replaced with this one-liner:
TrimPeriods = trim(replace(strInput,".",""))and you might consider a small change to:
TrimPeriods = application.trim(replace(strInput,".",""))which will reduce multiple spaces between words to single spaces too.

hybmg57
04-26-2010, 04:21 AM
Hi, I have tried the way you suggested but had no luck. Any ideas? I'm not sure why it can be saved once and then when you open that generated Excel spreadsheet it doesn't allow to save once again to replace the current one.

p45cal
04-26-2010, 05:38 AM
Could you try something for me? When I tried this on an xl2003 machine with xp I got an error before even encountering the SaveAs line which turned out to be because of a reference to MicroSoft Outlook 12.0 object library, which showed as missing. On my machine this was because Outlook 2003 is installed.
Can you write yoursef a brief sub like this:
Sub blah()
fn = ThisWorkbook.FullName
MsgBox "about to save as:'" & fn & "'", , "FYI"
ThisWorkbook.SaveAs Filename:=fn
End Sub and run it. Do you get the same error? If so, remove the tick next to the MISSING: Microsoft Outlook 12.0 object library entry in the vbe's Tool|References (that's if there is one!) and try again.
Still an error?

Another avenue to explore is access rights and the conflictresolution argument in the Saveas method.

hybmg57
04-26-2010, 06:47 AM
Hi, thank you very much for your help but I've tried unticking missing reference and reselected a new 11.0 Outlook reference which allowed to run this properly on the computer without issues. But occasionally we do need to go into the actual file which is saved in the server "hivol" and fill in "rngRevision" or "cell F2" with two digit number and run the "Submit Report" macro again so go through the process and overwrite on the existing file. This is when I get the error. But I'm not sure if this is actual desktop related issue as it is working fine with Windows 7 and Excel 2007.

I'm not sure about access rights and conflict resolution argument in the Saveas method. Is this an option for me? If so, are you able to elaborate this for me?

Thank you!!!

p45cal
04-26-2010, 07:07 AM
You've confused me more than clarified the problem.
You say:"...which allowed to run this properly on the computer without issues..."
By this, do you mean everything worked and you were able to save the file on top of itself without an error on the line you first cited?

If not, what were the 'issues' that this solved.

Actually, I'll stop there, because I could type forever saying try this then if that then try another with loads of different options, but I haven't the time, so we'll do this small step by small step and get to the root cause. Then we'll find a way to fix it.

I should be at my desk most of the afternoon and evening(UK) so will check back regularly.

hybmg57
04-26-2010, 08:07 AM
Sorry if I confused you... Yes everthing worked fine and I was able to save on top of the file created as first cited with Excel 2007 and Windows 7. When we create reports, we first save PDF and workbook with unique syntax (which is generated based on what is given in Excel cells) in "hivol" server. Then later we go to the "hivol" server where the file is saved which is generated by the macro (not the template) and run the macro again after filling in the Rev number. This should come with overwrite prompt and save PDF again then comes up with new Outlook message with attachement PDF. I'm not sure why this is not working as error itself is quite wierd...Basically it just does not work with Excel 2003 and Windows XP version...Not sure what it is but I'm a VBA developer myself. It's probably something to do with server setting with access rights of the specific desktop but IT guys confirms that all access is given and there is problem with file/macro which is interesting...

Thank you for your help

p45cal
04-26-2010, 08:21 AM
Everything was going swimmingly until you said: "as first cited with Excel 2007 and Windows 7".

But you first cited:"This error is coming on a computer with Windows XP and it's Excel 2003."

So,
1. Working on the computer with xl2003 and XP,
2. you "untick missing reference and reselect a new 11.0 Outlook reference"

does everything work and you are able to save the file on top of itself without an error on the line with SaveAs?

hybmg57
04-26-2010, 11:22 PM
Hi, thanks for the reply. Sorry I meant it is working on Excel 2007 but not 2003. After doing what you have mentioned, I get the same error...

p45cal
04-27-2010, 12:25 AM
Right, so :
1. Working on the machine with xl2003 and XP
2. ..on a file which gives the error
3. untick all and any reference to Outlook in vbe tools|references
4. add a new standard code module (so that we can delete all this investigative code easily later by removing the module)
5. Add the code in message 4 and run it.

What happens?