Need help debugging simple macro

[imafellow]

Hi, all,

When I run this script I get "Run-time error 424: Object equired"

Sub SaveAsCellContent()
ActiveWorkbook.SaveAs Filename:="D:\WORK\=ACCOUNTING\Invoices
Pending\Invoice - " & Range("Invoice!C7") & " - " & Range(Invoice!A7) &
" - " & Range(Invoice!A15) & ".xls"
End Sub

As may be evident I know little to no VBA.
What am I doing wrong?

Thanks,


KZ

 

[Trevor Shuttleworth]

Change the references with the format:

Range("Invoice!C7")

to this format:

Sheets("Invoice").Range("C7")

Regards

Trevor

 

[Mallycat]

It's hard to work out what you are doing here. Try adding the file nam
to a variable first.

Put a break in the saveas line (click in the margin on the left han
side of the VBA code so it creates a maroon dot), then when the cod
runs, it will stop and give you a chance to see what X actuall equals
hover over the variable so you can see what it equals.

x="D:\WORK\=ACCOUNTING\Invoices
Pending\Invoice - " & Range("Invoice!C7") & " - " & Range(Invoice!A7
&
" - " & Range(Invoice!A15) & ".xls"

ActiveWorkbook.SaveAs Filename:=x

Also, check if the =ACCOUNTING in the file address is correct.

Mat

 

[imafellow]

I'll try what you guys recommend.

Also, check if the =ACCOUNTING in the file address is correct.

It's correct. I use an equals sign to make the folder easy to spot, and
to keep it at the top of the window when I sort by filename in
explorer.

KZ

 

[JE McGimpsey]

Try:

Sub SaveAsCellContent()
ActiveWorkbook.SaveAs Filename:= _
"D:\WORK\=ACCOUNTING\Invoices Pending\Invoice - " & _
Range("Invoice!C7") & " - " & Range("Invoice!A7") & _
" - " & Range("Invoice!A15") & ".xls"
End Sub

 

[JE McGimpsey]

That shouldn't be necessary. However, he forgot to enclose two of the
range references in quotes.

 

[imafellow]

Excellent!

It works with the following code:

Sub SaveAsCellContent()
ActiveWorkbook.SaveAs Filename:="D:\WORK\=ACCOUNTING\Invoices Pending\"
& "Invoice - " & Sheets("Invoice").Range("C8") & " - " &
Sheets("Invoice").Range("A7") & " - " & Sheets("Invoice").Range("A15")
& ".xls"
End Sub

The only problem I have now is in the formatting of the contents of a
particular cell. Perhaps someone can offer a solution:

I've designed my invoice numbering to be based on the invoice time and
date, which I enter manually. My invoice number formatted yymmddhhmm.
So, for example, an invoice dated Monday, July 17, 2006, 6:40 pm will
have the invoice number 0607171840 (I know... the string has a leading
zero which can lead to calculation errors, but I'll deal with that
later).

Cell C7, formatted as I mentioned above, contains the formula C7=B7.
B7 contains the invoice number derrives its value from B7, which is
where I enter the date and time.

The problem is that when the macro enters the contents of C7 into the
file name, it ignores the custom date formatting I've applied to the
cell, and tries to insert the full date and time, which fails, of
course, because it contains illegal characters.

How do I get the macro to maintain my custom formatting?

KZ

 

[imafellow]

Do the underscores at the the ends of your lines indicate a line break?
If I want to put everything on one line do I have to remove the
underscores?

KZ

 

[JE McGimpsey]

Hard to tell what to suggest, since C7 contains the invoice number, but
none of the references in your code are to cell C7...

However, if one of them *were* to refer to C7, you could use the .Text
property instead of the .Value property:

...& Sheets("Invoice").Range("C7").Text...

 

[JE McGimpsey]

Yes and Yes. See "line-continuation character" in XL/VBA Help.

 

[imafellow]

Oh, oops. Yeah, my code says C8 but it's really C7.
Typo.
Sorry, and thanks for the tip.

I'm gonna test it right now and get back to you here.

KZ

 

[imafellow]

JE, that did it, thank you very much!

And thanks, also, to everyone else who helped me with this problem!

Much appreciated!

KZ