Formula with an apostrophe...

This is probably a really dumb question but im new to this. I'm trying to put in a code for VB to automatically insert a formula into a specific cell. My formula has an apostrophe in it because it refers to another workbook...

ActiveSheet.Range("B66").Formula = "=pull("'"&mfpn&wsn&"'!"&ct)"

Everything after the first quotation is converted into a comment...Is there a work around?

please help!!

single quotes will help... here is a working line of code from one of my apps...


ActiveChart.SeriesCollection(NumSheets - OSheets).XValues = "='" & OutSheet & "'!R" & Start & "C" & fCol & ":R" & Series & "C" & fCol

I tried that but it didnt work :-(

Any other suggestions?

Asuuming those are all variables as in the example below, then
Const mfpn As String = "[Book3]"
Const wsn As String = "Sheet 1"
Const ct As String = "A1"

Range("B66").Formula = "=pull('" & mfpn & wsn & "'!" & ct & ")"
... stores the formula =pull('[Book3]Sheet 1'!A1)

no they arent variables, they are cell references for the Pull function to work I have to refer to the cell references in the formula

there isnt a quick fix for this?

The problem with the concatenation you posted originally was that the single quote occured outside of the literal string segments, i.e. wasn't enclosed by double quotes. Both of the above suggestions fix that.
You also want to make sure that there is always a space on either side of the concatenator (&), otherwise VB will not interpret the symbol correctly.

If it still doesn't work, please describe the new problem, as the original one is resolved... :confused:

ok So this was my original code....

ActiveSheet.Range("B66").Formula = "=pull("'"&mfpn&wsn&"'!"&ct)"

Which theoretically I just want this formula to be put in cell B66....

=pull("'"&mfpn&wsn&"'!"&ct)

Now you are saying to make my code look like this....

ActiveSheet.Range("B66").Formula = "=pull(""'"" & mfpn & wsn & ""'"!" & ct)"

When I do that, I get....

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Expected: end of statement
---------------------------
OK Help
---------------------------

I dunno. shg's version of your concatenation is certainly syntactically correct, but you say it's not what you want. What's wrong with it?

So this is the code I used through SHG's comment

Const mfpn As String = "Y:\1 Accounting Forms\2008 MIP\2008 Accounting Forms Revised\NEW\Linked Accounts\[allaccounts.xls]"
Const wsn As String = "CheckRequest"
Const ct As String = "$h$13"
Range("B67").Formula = "=pull('" & mfpn & wsn & "'!" & ct & ")"

The problem with it is that this is what gets inserted...

=pull([allaccounts.xls]CheckRequest!$H$13)

For the Pull() function to work the full path for the file needs to be inserted not just the file name.

I'm afraid I still don't understand where the problem lies. "mfpn" is your variable, so if you need it to contain the full path, set it to the full path. If you need it to contain just the filename, set it to just the filename. :confused:

Edit: Wait - does the cell $H$13 contain the path/filename that you want to feed into the pull function? Or does the workbook specified by that path contain the cell $H$13?

Yes i set the whole path, and the whole path doesnt come into excel... only the file name comes into excel for some reason.




Please post Excel questions, in the Excel forum.

Please use the .. tags when you post your code.

Thank you.

Yes i set the whole path, and the whole path doesnt come into excel... only the file name comes into excel for some reason.
Okay, I see what you mean now, sorry to be so slow. :)
When the other workbook is open, the formula will be displayed without the path. When you close it, it's displayed with the full path. So what you describe can't be the reason if the formula still isn't working the way it should.

If you want the full path passed to the function, you can pass the path and cell reference as strings and modify the function appropriately:
Function Pull(sFile as String, sRef as String) as Whatever

When the other workbook is open, the formula will be displayed without the path. When you close it, it's displayed with the full path.
There's a good rationale for that, deevooneh. The formula refers to the open workbook. If you make changes to the open workbook, those changes are reflected in the results of the formula, even though the workbook on disk is unchanged.

Further, if you save that workbook as another name and then close it, Excel will dutifully revise the formula to refer to the full path of the saved-as workbook.

Thats great advice SHG thanks it worked!!!!!