Error when running macro

[Rob]

Hi,

I have the following code that runs fine when the file is opened from my
directory, however when opened from a file attached to an email, a code 9
error occurs. I think it's to do with the file name when opened from an
email being ~7341470.xls (or similar) whereas it has a proper name when
opened from a directory. Is there a way to overcome this, perhaps by
checking whether the file has ~ in its name?

Dim iFname As String
Range("A1").Formula = _
"=VLOOKUP(D2,[" + iFname + "]Database,2,False)"

Thanks, Rob

 

[Dave Peterson]

difficult to guess, without knowing what's in iFname.

But did you include the exclamation point?

And did you include the single quotes around the filename?

And if the Database is in the same workbook, you could drop all that stuff that
refers to another workbook.

And VBA is very forgiving, but I'd use & to concatenate strings.
+ is usually used for adding numbers.

Hi,

I have the following code that runs fine when the file is opened from my
directory, however when opened from a file attached to an email, a code 9
error occurs. I think it's to do with the file name when opened from an
email being ~7341470.xls (or similar) whereas it has a proper name when
opened from a directory. Is there a way to overcome this, perhaps by
checking whether the file has ~ in its name?

Dim iFname As String
Range("A1").Formula = _
"=VLOOKUP(D2,[" + iFname + "]Database,2,False)"

Thanks, Rob

 

[Rob]

Thanks Dave, single quotes was the problem. Rob

difficult to guess, without knowing what's in iFname.

But did you include the exclamation point?

And did you include the single quotes around the filename?

And if the Database is in the same workbook, you could drop all that stuff
that
refers to another workbook.

And VBA is very forgiving, but I'd use & to concatenate strings.
+ is usually used for adding numbers.

Hi,

I have the following code that runs fine when the file is opened from my
directory, however when opened from a file attached to an email, a code 9
error occurs. I think it's to do with the file name when opened from an
email being ~7341470.xls (or similar) whereas it has a proper name when
opened from a directory. Is there a way to overcome this, perhaps by
checking whether the file has ~ in its name?

Dim iFname As String
Range("A1").Formula = _
"=VLOOKUP(D2,[" + iFname + "]Database,2,False)"

Thanks, Rob