I have written a macro that manipulates multiple workbooks. Rather
than require the workbooks to be named a specific name I want to ask
the user for the name and use that within my spreadsheet.

I used the following command to get the file name from the user:

openfilenameA = Application.GetOpenFilename _
(filefilter:="Excel Files (*.xls), *.xls", Title:="Select input
datafile from ADP")

The only problem with the variable "openfilenameA" was that it
contained the full path of the file. My next thought was to open up
the file and then use the command:

ADPFile = ThisWorkbook.Name

My only problem is that once I do this, the value assigned to ADPFile
variable is actually the name of my macro spreadsheet that I am
running.

My process is to have the user open up my macro sheet and then execute
the macro from that. This appears to be getting in the way of things.
Any ideas on how to fix?

Todd

I think I'd just use a workbook variable that represents that workbook and never
worry about the name:

Dim OpenFileNameA as variant
dim WkbkA as workbook

openfilenameA = Application.GetOpenFilename _
(filefilter:="Excel Files (*.xls), *.xls", _
Title:="Select input datafile from ADP")

if openfilenamea = false then
exit sub 'user hit cancel
end if

set wkbka = workbooks.open(filename:=openfilenameA)

'then do things by referring to that open workbook

with wkbka.worksheets(1)
.range("a1").value = "hi there!"
end with

wkbka.close savechanges:=true



wrote:

I have written a macro that manipulates multiple workbooks. Rather
than require the workbooks to be named a specific name I want to ask
the user for the name and use that within my spreadsheet.

I used the following command to get the file name from the user:

openfilenameA = Application.GetOpenFilename _
(filefilter:="Excel Files (*.xls), *.xls", Title:="Select input
datafile from ADP")

The only problem with the variable "openfilenameA" was that it
contained the full path of the file. My next thought was to open up
the file and then use the command:

ADPFile = ThisWorkbook.Name

My only problem is that once I do this, the value assigned to ADPFile
variable is actually the name of my macro spreadsheet that I am
running.

My process is to have the user open up my macro sheet and then execute
the macro from that. This appears to be getting in the way of things.
Any ideas on how to fix?

Todd

--

Dave Peterson

Hi Todd,

Try

ADPFile = ActiveWorkbook.Name

after opening the workbook

--
HTH

Bob Phillips

wrote in message
ups.com...
I have written a macro that manipulates multiple workbooks. Rather
than require the workbooks to be named a specific name I want to ask
the user for the name and use that within my spreadsheet.

I used the following command to get the file name from the user:

openfilenameA = Application.GetOpenFilename _
(filefilter:="Excel Files (*.xls), *.xls", Title:="Select input
datafile from ADP")

The only problem with the variable "openfilenameA" was that it
contained the full path of the file. My next thought was to open up
the file and then use the command:

ADPFile = ThisWorkbook.Name

My only problem is that once I do this, the value assigned to ADPFile
variable is actually the name of my macro spreadsheet that I am
running.

My process is to have the user open up my macro sheet and then execute
the macro from that. This appears to be getting in the way of things.
Any ideas on how to fix?

Todd

If you just need to get the filename from a path you could use:

Dim openfilefullnameA As String
Dim openfilenameA As String
openfilefullnameA = Application.GetOpenFilename _
(filefilter:="Excel Files (*.xls), *.xls", Title:="Select input
datafile from ADP")
openfilenameA = LCase$(Mid$(openfilefullnameA, InStrRev(openfilefullnameA,
"\") + 1))


wrote in message
ups.com...
I have written a macro that manipulates multiple workbooks. Rather
than require the workbooks to be named a specific name I want to ask
the user for the name and use that within my spreadsheet.

I used the following command to get the file name from the user:

openfilenameA = Application.GetOpenFilename _
(filefilter:="Excel Files (*.xls), *.xls", Title:="Select input
datafile from ADP")

The only problem with the variable "openfilenameA" was that it
contained the full path of the file. My next thought was to open up
the file and then use the command:

ADPFile = ThisWorkbook.Name

My only problem is that once I do this, the value assigned to ADPFile
variable is actually the name of my macro spreadsheet that I am
running.

My process is to have the user open up my macro sheet and then execute
the macro from that. This appears to be getting in the way of things.
Any ideas on how to fix?

Todd