Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from various
sheets in that file.

I have tried the following just to get myself started but with no success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks


--
Steve R

see if this helps:

in b4 of my summary workbook, it enters the formula
Range("B4") = "='" & fPath & FName & "Trans'!$G" & rNum


these are my variable settings:
fPath = "" '"N:\My Documents\Excel\"
FName = Worksheets("tellers").Cells(i, "B").Value
Trans is the actual sheet name in the unopened workbook
the $G is the column in the unopened workbook
rNum is the row number of the cell i want, $G4

--


Gary


"Steve" wrote in message
...
Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from
various
sheets in that file.

I have tried the following just to get myself started but with no success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks


--
Steve R

see if this helps:

in b4 of my summary workbook, it enters the formula
Range("B4") = "='" & fPath & FName & "Trans'!$G" & rNum


these are my variable settings:
fPath = "" '"N:\My Documents\Excel\"
FName = Worksheets("tellers").Cells(i, "B").Value ' i have a list of
filenames in B in this format, [filename.xls]
Trans is the actual sheet name in the unopened workbook
the $G is the column in the unopened workbook
rNum is the row number of the cell i want, $G4


--


Gary


"Steve" wrote in message
...
Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from
various
sheets in that file.

I have tried the following just to get myself started but with no success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks


--
Steve R

Sorry Gary, having a blond moment.

How can I apply this to my situation. I already have the file name
(including path etc.)

Cheers
--
Steve R


"Steve" wrote:

Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from various
sheets in that file.

I have tried the following just to get myself started but with no success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks


--
Steve R

you don't need to open the file to get the cell data, i am assuming that's
what you want to do.

not sure what you're trying to accomplish.

in my example, i am getting the value from an unopened workbook, which is
the filename.xls, from the sheet called trans and the cell G4. i also have a
sheet called hours, so the formula is exactly the same, except trans is
replaced with hours.

Range("C4") = "='" & fPath & FName & "Hours'!$G" & rNum

so spelled out the formula in B4 is:
='N:\My Documents\Excel\Teller\[Andrea.xls]Trans'!$G4

and C4 is:
='N:\My Documents\Excel\Teller\[Andrea.xls]Hours'!$G4


--


Gary


"Steve" wrote in message
...
Sorry Gary, having a blond moment.

How can I apply this to my situation. I already have the file name
(including path etc.)

Cheers
--
Steve R


"Steve" wrote:

Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from
various
sheets in that file.

I have tried the following just to get myself started but with no
success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks


--
Steve R

FileToOpen at this point is the entire Path ie. "C:/My
Documents/Excel/MyWorkbook.xls". We need to get rid of the Path and the
extension

If fileToOpen = "False" Then 'if no file is selected
Exit Sub
End If
myFileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1, 255) 'get rid
of path
myFileName = Left(myFileName, Len(myFileName) - 4) 'get rid of extension
'myFileName now equals "MyWorkbook"
Name = Workbooks(myFileName).Worksheets("Sheet1").Range(" A7").Value

Mike F
"Steve" wrote in message
...
Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from
various
sheets in that file.

I have tried the following just to get myself started but with no success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks


--
Steve R

I now get a subscript out of range error.

Essentially my customers will all have different file names with the same
info in them and I need them to select the file which could be in any
directory.

Cheers


Dim myFileName As Variant 'in case user cancels

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

If myFileName = False Then
MsgBox "try later" 'cancel
Exit Sub
End If

' get rid of Path
myFileName = Mid(myFileName, InStrRev(myFileName, "\") + 1, 255)

' get rid of extension
myFileName = Left(myFileName, Len(myFileName) - 4)

MsgBox myFileName

' myFileName now equals "MyWorkbook"
Name = Workbooks(myFileName).Worksheets("MSD").Range("A7" ).Value


--
Steve R


"Mike Fogleman" wrote:

FileToOpen at this point is the entire Path ie. "C:/My
Documents/Excel/MyWorkbook.xls". We need to get rid of the Path and the
extension

If fileToOpen = "False" Then 'if no file is selected
Exit Sub
End If
myFileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1, 255) 'get rid
of path
myFileName = Left(myFileName, Len(myFileName) - 4) 'get rid of extension
'myFileName now equals "MyWorkbook"
Name = Workbooks(myFileName).Worksheets("Sheet1").Range(" A7").Value

Mike F
"Steve" wrote in message
...
Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from
various
sheets in that file.

I have tried the following just to get myself started but with no success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks


--
Steve R

OK. We need to open the workbook, assign A7 to variable "name", and close
the workbook. In that case we can skip the get rid of extension lines.
Also name needs to be Dim name As String, also myFileName should be String.

Dim myFileName As String
Dim name As String

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

If myFileName = False Then
MsgBox "try later" 'cancel
Exit Sub
End If
Workbooks.Open Filename:=myFileName
' get rid of Path
myFileName = Mid(myFileName, InStrRev(myFileName, "\") + 1, 255)

MsgBox myFileName

' myFileName now equals "MyWorkbook.xls"
name = Worksheets("MSD").Range("A7").Value 'see note
Workbooks(myFileName).Close

Note: since workbook FileToOpen is now open, it it also the Active Workbook,
so all we need is the worksheet range as a reference.

Mike F
"Steve" wrote in message
...
I now get a subscript out of range error.

Essentially my customers will all have different file names with the same
info in them and I need them to select the file which could be in any
directory.

Cheers


Dim myFileName As Variant 'in case user cancels

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

If myFileName = False Then
MsgBox "try later" 'cancel
Exit Sub
End If

' get rid of Path
myFileName = Mid(myFileName, InStrRev(myFileName, "\") + 1, 255)

' get rid of extension
myFileName = Left(myFileName, Len(myFileName) - 4)

MsgBox myFileName

' myFileName now equals "MyWorkbook"
Name = Workbooks(myFileName).Worksheets("MSD").Range("A7" ).Value


--
Steve R


"Mike Fogleman" wrote:

FileToOpen at this point is the entire Path ie. "C:/My
Documents/Excel/MyWorkbook.xls". We need to get rid of the Path and the
extension

If fileToOpen = "False" Then 'if no file is selected
Exit Sub
End If
myFileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1, 255) 'get
rid
of path
myFileName = Left(myFileName, Len(myFileName) - 4) 'get rid of
extension
'myFileName now equals "MyWorkbook"
Name = Workbooks(myFileName).Worksheets("Sheet1").Range(" A7").Value

Mike F
"Steve" wrote in message
...
Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from
various
sheets in that file.

I have tried the following just to get myself started but with no
success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks


--
Steve R

Are you gonna open the file to get the value?

dim wkbk as workbook
dim myFileName as variant
dim MyValue as variant

myfilename = application.getopenfilename("Excel files, *.xls")
if myfilename = false then
exit sub
end if

set wkbk = workbooks.open(filename:=myfilename)
myvalue = wkbk.worksheets("sheet1").range("a7").value
wkbk.close savechanges:=false

========
But John Walkenbach has a routine that can get values from a closed workbook:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

Steve wrote:

Hi

I think I should have asked this with my earlier question.

I now have the file name using getopenfilename.

However, I now want to start getting various bit of information from various
sheets in that file.

I have tried the following just to get myself started but with no success.

Name = myFileName.worrksheets("sheet1").Range("A7").Value

where my filename is the variable used for the return fileanme.

Please help

Many thanks

--
Steve R

--

Dave Peterson