Filename Issue
 

I am writing a program where I prompt the user to save as: filename.

Later on I want to reference that workbook and pull a few cells out o
it. If I new the file name I would write:

Workbooks("book1.xls).Sheets("Sheet1").Range("A1") .Value

but since I don't know the filename I don't know how to reference it.
I would think it would be something like:

Workbooks(filename).Sheets("Sheet1").Range("A1").V alue or something bu
it isn't working for me.

While the sheet is open should I do a "find root directory and fil
name" routine and some how name that "filename"?

Any help would be appreciated.

Zeroma

--
Zeroma
-----------------------------------------------------------------------
Zeroman's Profile: http://www.excelforum.com/member.php...fo&userid=1662
View this thread: http://www.excelforum.com/showthread.php?threadid=31495

Filename Issue
 

Hi Zeroman,

Below is some code a wrote to remove formulas on all worksheets of an *.xls
file and save it as separate copy taking the name from the user. Maybe it
will give you the direction.
Regards,
KL

'--------------Macro Start------------------
Const PASSWORD = "password"

Sub CopyBookWitoutFormulas()
Dim s As Worksheet
Dim thisFile As String
Dim newFile As Variant

thisFile = ThisWorkbook.Name
newFile = Application.GetSaveAsFilename _
(thisFile, "Microsoft Excel Workbooks (*.xls),*.xls")

Select Case VarType(newFile)
Case vbBoolean
MsgBox "Operation Canceled!"
Case Else
Application.ScreenUpdating = False
For Each s In ThisWorkbook.Worksheets
With s
Select Case .ProtectionMode
Case True
.Activate
With .Cells
.Copy
.PasteSpecial xlPasteValues
End With
.Range("A1").Select
Case False
.Unprotect PASSWORD
.Activate
With .Cells
.Copy
.PasteSpecial xlPasteValues
End With
.Range("A1").Select
.Protect PASSWORD
End Select
Next s
Application.ScreenUpdating = True
On Error GoTo ErrorHandler
ActiveWorkbook.SaveAs Filename:= newFile
End Select
Exit Sub
ErrorHandler:
MsgBox "File has not been saved!"
End Sub
'--------------Macro End------------------

"Zeroman" wrote in message
...

I am writing a program where I prompt the user to save as: filename.

Later on I want to reference that workbook and pull a few cells out of
it. If I new the file name I would write:

Workbooks("book1.xls).Sheets("Sheet1").Range("A1") .Value

but since I don't know the filename I don't know how to reference it.
I would think it would be something like:

Workbooks(filename).Sheets("Sheet1").Range("A1").V alue or something but
it isn't working for me.

While the sheet is open should I do a "find root directory and file
name" routine and some how name that "filename"?

Any help would be appreciated.

Zeroman


--
Zeroman
------------------------------------------------------------------------
Zeroman's Profile:
http://www.excelforum.com/member.php...o&userid=16628
View this thread: http://www.excelforum.com/showthread...hreadid=314957

Filename Issue
 

You can set a reference to the workbook in your code before you do the saveas

Dim Wb As Workbook
Set Wb = ActiveWorkbook

You can use this then
WB.Sheets("Sheet1").Range("A1").Value




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Zeroman" wrote in message ...

I am writing a program where I prompt the user to save as: filename.

Later on I want to reference that workbook and pull a few cells out of
it. If I new the file name I would write:

Workbooks("book1.xls).Sheets("Sheet1").Range("A1") .Value

but since I don't know the filename I don't know how to reference it.
I would think it would be something like:

Workbooks(filename).Sheets("Sheet1").Range("A1").V alue or something but
it isn't working for me.

While the sheet is open should I do a "find root directory and file
name" routine and some how name that "filename"?

Any help would be appreciated.

Zeroman


--
Zeroman
------------------------------------------------------------------------
Zeroman's Profile: http://www.excelforum.com/member.php...o&userid=16628
View this thread: http://www.excelforum.com/showthread...hreadid=314957

Filename Issue
 

after the saveas operation is complete, you can capture in
a string.

Dim z As String
z = ActiveWorkbook.Name


-----Original Message-----

I am writing a program where I prompt the user to save
as: filename.

Later on I want to reference that workbook and pull a few
cells out of
it. If I new the file name I would write:

Workbooks("book1.xls).Sheets("Sheet1").Range("A1" ).Value

but since I don't know the filename I don't know how to
reference it.
I would think it would be something like:

Workbooks(filename).Sheets("Sheet1").Range("A1"). Value or
something but
it isn't working for me.

While the sheet is open should I do a "find root
directory and file
name" routine and some how name that "filename"?

Any help would be appreciated.

Zeroman


--
Zeroman
----------------------------------------------------------
--------------
Zeroman's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=16628
View this thread:
http://www.excelforum.com/showthread...hreadid=314957

.