Filename Issue

[Zeroman]

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").Value 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

 

[KL]

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------------------

 

[Ron de Bruin]

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

 

[DMoney]

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

Dim z As String
z = ActiveWorkbook.Name