Solved: Problem with an excel macro 400 error

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

bluetrane_uk

Thread Starter
Joined
Apr 11, 2013
Messages
20
The following macro works perfectly on the Mac I have built it on. But when I transfer it to my server - or try to run it on a Windows machine - it returns a 400 error.

Does anybody know why?

<code>
Sub NextInvoice()
Range("K9").Value = Range("K9").Value + 1
Range("F34:K40").ClearContents
Range("F7:F11").Value = "Name/Address"
Range("K7").ClearContents
Range("F29:K30").Value = "Personal message..."
Range("G21:K21").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = Range("B3").Value
ActiveWorkbook.SaveAs ActiveWorkbook.Path + "\" + NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
ActiveWorkbook.Close SaveChanges:=True
End Sub
</code>
 

bluetrane_uk

Thread Starter
Joined
Apr 11, 2013
Messages
20
Ok that seems to work better - I no longer get the 400 error... Now I am getting an error message specifically relating to this line...

ActiveWorkbook.SaveAs ActiveWorkbook.Path + "\" + NewFN, FileFormat:=xlOpenXMLWorkbook

ANy ideas?
 
Joined
Feb 21, 2013
Messages
520
Try replacing the 2nd macro with this

Code:
 Sub SaveInvWithNewName()
 Dim NewFN As Variant
 ' Copy Invoice to a new workbook
 myPath = ActiveWorkbook.Path
 ActiveSheet.Copy
 NewFN = Range("B3").Value
 ActiveWorkbook.SaveAs myPath + "\" + NewFN + ".xlsx"
 ActiveWorkbook.Close
 NextInvoice
 ActiveWorkbook.Close SaveChanges:=True
 End Sub
 

bluetrane_uk

Thread Starter
Joined
Apr 11, 2013
Messages
20
Hi, unfortunately the new code returned the same problem with the same line...

ActiveWorkbook.SaveAs myPath + "\" + NewFN + ".xlsx"

Is it the slash that is causing the problem... on my mac it is certainly not needed?
 

bluetrane_uk

Thread Starter
Joined
Apr 11, 2013
Messages
20
The error messages are different depending on whether I run it from the server or from the windows desktop.

From the server it simply says there is a problem with that line: runtime error 1004 "method 'saveas of object '_workbook' failed".

From the windows environment it is more complex and says: runtime error 1004 "Can't access file c:[random letters and numbers]" there are several possible reasons for this blah blah.
 

bluetrane_uk

Thread Starter
Joined
Apr 11, 2013
Messages
20
The error messages are different depending on whether I run it from the server or from the windows desktop.

From the server it simply says there is a problem with that line: runtime error 1004 "method 'saveas of object '_workbook' failed".

From the windows environment it is more complex and says: runtime error 1004 "Can't access file c:[random letters and numbers]" there are several possible reasons for this blah blah.
 
Joined
Feb 21, 2013
Messages
520
I had the same problem but it resolved when I pulled out the path to a separate line. Try changing

myPath = ActiveWorkbook.Path

to

an explicit path e.g.

myPath="C:\user\name\documents"

also, if you are using Excel 2003 change ".xlsx" to "xls"
 
Joined
Feb 21, 2013
Messages
520
Sorry - it was really late here and needed some sleep

We need to see what is happening at each step of the process. I suspect there may be something wrong with the path or the filename.

When you are in VBA place your cursor anywhere in the SaveInvWithNewName and press F8. you will see that the macro starts. Each time you press F8 it will process the next line.

After it processes a line you will see how VBA interpreted the line in the Locals box which is usually the bottom part of the screen. (if it's not there go to VIEW/Locals Window.

After you hit F8 on the line that says

NewFN = Range("B3").Value


check the Locals window to see NewFN
do the same thing after you process myPath

Can you let me know exactly how those 2 variables are interpreted?
 

bluetrane_uk

Thread Starter
Joined
Apr 11, 2013
Messages
20
Aha!

: NewFN : Error 2042 : Variant/Error

: myPath : "Macintosh HD:Users:MYNAME:Desktop:Invoice trials" : Variant/String

So the error seems to be in the file name? Just so you know in cell B3 is the following =K7&" "&"Invoice"&" "&" "&H16
 

bluetrane_uk

Thread Starter
Joined
Apr 11, 2013
Messages
20
What I see is something like this: "BLAH Invoice THYG121210007"

The cell contains: =K7&" "&"Invoice"&" "&H16 - which generates that content.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top