Code not working on all computers

[andrewmorgan]

Hi

I have the following code in a workbook,

Dim newFile As String
newFile = "Clearance for" & " " & Range("B2")
ActiveWorkbook.SaveAs Filename:="filepath" & newFile

'reopen file
Workbooks.Open Filename:="Filepatch\Filename"
'close
Workbooks(newFile).Close SaveChanges = True


It works fine on my version of Excel but gives a debug error on my collegues version, saving the file name newFile is not valid.

I hope someone can help explain what is missing ?

Andrew

 

[bensonsearch]

hi,
this line i have an issue with

Workbooks.Open Filename:="Filepatch\Filename"

should this not be

workbooks.open filename:=filepath & "\" & newfile

 

[andrewmorgan]

Hi

Sorry I have not explaned this correctly, what this piece of code does on my computer is save the curretly file "Clearance Instructions" as Newfile, then I want it to close newFile and reopen the original "Clearance Instructions".

The code actually shows as follows, but I took out the filepatch and name to make it simplier.

Dim newFile As String
newFile = "Clearance for" & " " & Range("B2")
ActiveWorkbook.SaveAs Filename:="H:\Clearance Instructions\" & newFile

Workbooks.Open Filename:="H:\Imports_27\Clearance Instructions.xls"

Workbooks(newFile).Close SaveChanges = True

Hope this helps.
Andrew

 

[jbeaucaire]

Although I still see small problems with the code shown, your question is about saving on different computers. The SAVEAS command on Excel 2007+ requires you include the extension, 2003 does not. Are the "versions" of Excel different, too?

 

[andrewmorgan]

I am sure there is loads wrong with most of my code, I tend to just use bits and pieces dotted across forums such as here, I am far from a programmer although I dabble !

Both versions of excel are 2002 SP3. I have tried this on other machine and it seems to work fine. Is there an addon or something that could be missing ? I just don't understand how it can work on one machine but not the other where all the machines should be the same.

 

[jbeaucaire]

If you put OPTION EXPLICIT at the top of your code modules, you turn on "code checking" and Excel will highlight problem code as you make the errors, keeps them from piling up.

When the code fails on the other machine does it give an error? Bensonsearch already noted the proper syntax for the SaveAs command, assuming filepath is another declared variable.

 

[andrewmorgan]

filepath is not another variable, it is a fixed file patch eg "c:\documents" but I did not want to post as it is very long winded.

I get the following error on the other machine, Run-time error 9 Subscript out of range.

Then it highlights to following line in VBA
Workbooks(newFile).Close SaveChanges = True

 

[jbeaucaire]

Subscript out of range means a named object cannot be located, usually.

The only named object is your workbook with the variable name inside. It can't find that. When you DEBUG and hver your mouse over that variable, what exactly is in there? Does it exactly resolve fully to the exact name of an open workbook?

 

[andrewmorgan]

It actually works on my PC and other peoples PC which is the strange thing.

Does excel "forget" strings after a while ?

i.e. I am telling excel what newFile = further up the code and refering to it again later.
In my excel this works fine but could the other excel "forget" what this newfile = and thus does not recognise the name ?

How do I check/change these setting in VBA ? I can understand if the code flat out does not work, as my coding is generally useless, but it actually does but not on all people pc's !

 

[jbeaucaire]

I'd start by making a list of the differences in operating system and Excel versions between the PCs, see if anything worth noting is found.