Excel 2003 - VBA - Remote access

C

C Brandt

Here is what I am trying to accomplish:
I have a spreadsheet where I would like to pull in parameters from another
spreadsheet.

I first used "GetOpenFileName" to get the name and path of the data file
then inserted this in a string that identifies the data that I am looking
for..

TSFileName = Application.GetOpenFilename(Title:="Open Current Trades
Sheet")
BuyStr = "'[" & TSFileName & "]Buy'!$A$8:$CL$500"
SellStr = "'[" & TSFileName & "]Sell'!$A$8:$CL$500"
MMStr = "'[" & TSFileName & "]Funds'!$B$8:$AE$500"

I used "formula" to populate the target cells then copied the values.

Range("I8").Formula = "=VLOOKUP($H8," & BuyStr & ",82,0)"
Range("J8").Formula = "=VLOOKUP($H8," & SellStr & ",82,0)"
Range("K8").Formula = "=VLOOKUP($H8," & MMStr & ",27,0)"
Range("L8").Formula = "=VLOOKUP($H8," & MMStr & ",26,0)"
Range("I8:L8").Select
Selection.AutoFill Destination:=Range("I8:L" & MaxRtn & ""),
Type:=xlFillDefault
Range("I8:L" & MaxRtn & "").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Here is what MMStr looks like:
'[C:\Documents and Settings\Craig Brandt\My Documents\MTA Working\Qtrly
Statement\Q407\Trades Sheet - 11-26-07.xls]Funds'!$B$8:$AE$500

Here is an example of the code generated for the Vlookup in J8.

=VLOOKUP($H8,'[C:\Documents and Settings\Craig Brandt\My Documents\MTA
Working\Qtrly Statement\Q407\[Trades Sheet - 11-26-07.xls]Buy]Trades Sheet -
11-26-07.xls]Buy'!$A$8:$CL$500,82,0)

Clearly, this is not correct. Why the double reference to the filename.

Help!
Craig
 
D

Dave Peterson

I think your lookup range in the =vlookup() formula is wrong to start.

If I selected a file named: c:\my documents\excel\book1.xls, then your string:
"'[" & TSFileName & "]Buy'!$A$8:$CL$500"
would resolve to:
'[c:\my documents\excel\book1.xls]Buy'!$A$8:$CL$500

When I create the same formula manually, I get a string like:
'C:\My Documents\excel\[book1.xls]Buy'!$A$8:$CL$500

Notice the position of your [ character.

It's time to parse that TSFileName and surround just the filename with []'s--not
the whole path & filename.

C said:
Here is what I am trying to accomplish:
I have a spreadsheet where I would like to pull in parameters from another
spreadsheet.

I first used "GetOpenFileName" to get the name and path of the data file
then inserted this in a string that identifies the data that I am looking
for..

TSFileName = Application.GetOpenFilename(Title:="Open Current Trades
Sheet")
BuyStr = "'[" & TSFileName & "]Buy'!$A$8:$CL$500"
SellStr = "'[" & TSFileName & "]Sell'!$A$8:$CL$500"
MMStr = "'[" & TSFileName & "]Funds'!$B$8:$AE$500"

I used "formula" to populate the target cells then copied the values.

Range("I8").Formula = "=VLOOKUP($H8," & BuyStr & ",82,0)"
Range("J8").Formula = "=VLOOKUP($H8," & SellStr & ",82,0)"
Range("K8").Formula = "=VLOOKUP($H8," & MMStr & ",27,0)"
Range("L8").Formula = "=VLOOKUP($H8," & MMStr & ",26,0)"
Range("I8:L8").Select
Selection.AutoFill Destination:=Range("I8:L" & MaxRtn & ""),
Type:=xlFillDefault
Range("I8:L" & MaxRtn & "").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Here is what MMStr looks like:
'[C:\Documents and Settings\Craig Brandt\My Documents\MTA Working\Qtrly
Statement\Q407\Trades Sheet - 11-26-07.xls]Funds'!$B$8:$AE$500

Here is an example of the code generated for the Vlookup in J8.

=VLOOKUP($H8,'[C:\Documents and Settings\Craig Brandt\My Documents\MTA
Working\Qtrly Statement\Q407\[Trades Sheet - 11-26-07.xls]Buy]Trades Sheet -
11-26-07.xls]Buy'!$A$8:$CL$500,82,0)

Clearly, this is not correct. Why the double reference to the filename.

Help!
Craig
Click to expand...
 
C

C Brandt

Dave:
Thanks for the input and testing proved you have the correct solution.
Is there a way to get just the path or filename?

Thanks Again,
Craig

Dave Peterson said:
I think your lookup range in the =vlookup() formula is wrong to start.

If I selected a file named: c:\my documents\excel\book1.xls, then your string:
"'[" & TSFileName & "]Buy'!$A$8:$CL$500"
would resolve to:
'[c:\my documents\excel\book1.xls]Buy'!$A$8:$CL$500

When I create the same formula manually, I get a string like:
'C:\My Documents\excel\[book1.xls]Buy'!$A$8:$CL$500

Notice the position of your [ character.

It's time to parse that TSFileName and surround just the filename with []'s--not
the whole path & filename.

C said:
Here is what I am trying to accomplish:
I have a spreadsheet where I would like to pull in parameters from another
spreadsheet.

I first used "GetOpenFileName" to get the name and path of the data file
then inserted this in a string that identifies the data that I am looking
for..

TSFileName = Application.GetOpenFilename(Title:="Open Current Trades
Sheet")
BuyStr = "'[" & TSFileName & "]Buy'!$A$8:$CL$500"
SellStr = "'[" & TSFileName & "]Sell'!$A$8:$CL$500"
MMStr = "'[" & TSFileName & "]Funds'!$B$8:$AE$500"

I used "formula" to populate the target cells then copied the values.

Range("I8").Formula = "=VLOOKUP($H8," & BuyStr & ",82,0)"
Range("J8").Formula = "=VLOOKUP($H8," & SellStr & ",82,0)"
Range("K8").Formula = "=VLOOKUP($H8," & MMStr & ",27,0)"
Range("L8").Formula = "=VLOOKUP($H8," & MMStr & ",26,0)"
Range("I8:L8").Select
Selection.AutoFill Destination:=Range("I8:L" & MaxRtn & ""),
Type:=xlFillDefault
Range("I8:L" & MaxRtn & "").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Here is what MMStr looks like:
'[C:\Documents and Settings\Craig Brandt\My Documents\MTA Working\Qtrly
Statement\Q407\Trades Sheet - 11-26-07.xls]Funds'!$B$8:$AE$500

Here is an example of the code generated for the Vlookup in J8.

=VLOOKUP($H8,'[C:\Documents and Settings\Craig Brandt\My Documents\MTA
Working\Qtrly Statement\Q407\[Trades Sheet - 11-26-07.xls]Buy]Trades Sheet -
11-26-07.xls]Buy'!$A$8:$CL$500,82,0)

Clearly, this is not correct. Why the double reference to the filename.

Help!
Craig
Click to expand...
Click to expand...
 
D

Dave Peterson

If you're using xl2k or newer, you could use instrrev() to look for the
rightmost backslash.

If you're using xl97 and older, you could loop from the right hand side looking
for the rightmost backslash.

Option Explicit
Sub testme()
Dim myStr As String
Dim myFolderName As String
Dim myFileName As String
Dim LastBackSlashPos As Long

myStr = "C:\my documents\excel\test\asdf.xls"

LastBackSlashPos = InStrRev(myStr, "\", -1, vbTextCompare)

If LastBackSlashPos = 0 Then
'no backslashes
Else
myFolderName = Left(myStr, LastBackSlashPos)
myFileName = Mid(myStr, LastBackSlashPos + 1)
End If

MsgBox myFolderName & vbLf & myFileName

End Sub

C said:
Dave:
Thanks for the input and testing proved you have the correct solution.
Is there a way to get just the path or filename?

Thanks Again,
Craig

Dave Peterson said:
I think your lookup range in the =vlookup() formula is wrong to start.

If I selected a file named: c:\my documents\excel\book1.xls, then your string:
"'[" & TSFileName & "]Buy'!$A$8:$CL$500"
would resolve to:
'[c:\my documents\excel\book1.xls]Buy'!$A$8:$CL$500

When I create the same formula manually, I get a string like:
'C:\My Documents\excel\[book1.xls]Buy'!$A$8:$CL$500

Notice the position of your [ character.

It's time to parse that TSFileName and surround just the filename with []'s--not
the whole path & filename.

C said:
Here is what I am trying to accomplish:
I have a spreadsheet where I would like to pull in parameters from another
spreadsheet.

I first used "GetOpenFileName" to get the name and path of the data file
then inserted this in a string that identifies the data that I am looking
for..

TSFileName = Application.GetOpenFilename(Title:="Open Current Trades
Sheet")
BuyStr = "'[" & TSFileName & "]Buy'!$A$8:$CL$500"
SellStr = "'[" & TSFileName & "]Sell'!$A$8:$CL$500"
MMStr = "'[" & TSFileName & "]Funds'!$B$8:$AE$500"

I used "formula" to populate the target cells then copied the values.

Range("I8").Formula = "=VLOOKUP($H8," & BuyStr & ",82,0)"
Range("J8").Formula = "=VLOOKUP($H8," & SellStr & ",82,0)"
Range("K8").Formula = "=VLOOKUP($H8," & MMStr & ",27,0)"
Range("L8").Formula = "=VLOOKUP($H8," & MMStr & ",26,0)"
Range("I8:L8").Select
Selection.AutoFill Destination:=Range("I8:L" & MaxRtn & ""),
Type:=xlFillDefault
Range("I8:L" & MaxRtn & "").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Here is what MMStr looks like:
'[C:\Documents and Settings\Craig Brandt\My Documents\MTA Working\Qtrly
Statement\Q407\Trades Sheet - 11-26-07.xls]Funds'!$B$8:$AE$500

Here is an example of the code generated for the Vlookup in J8.

=VLOOKUP($H8,'[C:\Documents and Settings\Craig Brandt\My Documents\MTA
Working\Qtrly Statement\Q407\[Trades Sheet - 11-26-07.xls]Buy]Trades Sheet -
11-26-07.xls]Buy'!$A$8:$CL$500,82,0)

Clearly, this is not correct. Why the double reference to the filename.

Help!
Craig
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Indirect Addressing in VBA 7
Excel 2003 - VBA - Sheet Referencing in Formulas 3

Top