Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Filename reference in Spreadsheet XML issue | Excel Discussion (Misc queries) | |||
Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
set filename to <filename-date on open | Excel Worksheet Functions | |||
Macro link issue when saving as new filename. | Excel Programming | |||
Saving filename same as import filename | Excel Programming |