You can grab cells from a closed file like this:
Sub ClosedGrabber()
ThisPath = "c:\documents and settings\what\"
ThisFile = "book2.xls"
ThisSheet = "sheet1"
ThisCell = "testrange"
ThisFile = "'" & ThisPath & "[" & ThisFile & "]" & ThisSheet & "'!" & ThisCell
Range("a1").Value = ExecuteExcel4Macro(ThisFile)
End Sub
Hi Rod, you don't need the full path of the workbook - just the name. (make sure its open though - or did you want it closed?)
Workbook = "Data Centralv1.xls"
PS: Although it works, I am hesitant to call a variable 'workbook' as it is also a VBA object name -it may cause conflicts - how about 'workbookx'?
Hi try this google search:
Google lookup
Hi Rod, it should work if you specify the other workbook. eg:
Workbooks("book2.xls").Sheets("sheet1").range("score").value
Also, you don't have to goto or select the range that you want to clear. Just identify it without going to it. eg:
Workbooks("book2.xls").Sheets(TEMPLATE_SHEET).Range("com1").ClearContents
XP is best for new machines. Everyone I know who upgraded a PC from Win98/2000 has had problems.
Hi & welcome to "THE BOARD".
Where are you double clicking?
In an email, in the excel file-open list?
First you would have to select a cell way to the right and to the bottom of the spreadssheet, then come back to your desired cell.
Range("aa65536").Select
Range("c80").select
If c1 is a percentage you can keep it formatted in your output like this:
=IF(ISBLANK(C1),"",A1&" "&B1&" "&C1*100&"%")
something like this in G1?
=IF(ISBLANK(C1),"",A1&" "&B1&" "&C1)
I like Derk's break-up suggestion. A search on "The memory could not be read" at microsoft.com suggests a memory buffer problem is the cause when it shows up in other MS apps. They suggest applying a 'service pack update' for the app but I'm not sure if there is one for Excel97.
Was the file originally created in Excel 97 or Excel 2000? There are some Excel 2000 VBA macro commands not found in Excel 97 that may result in compile or file-open errors.
Suggestions:
Can you open the file in 97 with macros disabled?
Have you tried opening the file in Excel2000 then saving it in Excel97 format?
Does this occur opening any file or just specific files?
Excel Everywhere@ $99 is porobably the closest.
You can also just upload the file to your site, but visitors will need to have Excel on their PC to view it & use it.
Did you create the buttons thru the control toolbox or thru the Forms toolbar? This will work on buttons created thru the control toolbox (View-Toolbars-Control Toolbox)
It should work if these macros are placed on the sheet's VBA module ie sheet1 module - not in module1, module2, workbook module etc...
Let us know.....
edit: is the button called commandbutton1 or commandbutton2, commandbutton3 etc...?
Try one of these:
Sub test1()
CommandButton1.Caption = Range("a1").Value
End Sub
or, if you want the caption to change automatically if the range content changes then:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = range("a1").Address Then
CommandButton1.Caption = Range("a1").Value
End If
End Sub
QuoteHave you noticed that it is always the boss that gives you his best spreadsheet and and asks you to analyze it for performance. You then discover that there are 100's of #DIV/0 errors so the data is useless. You then spend ages altering the formulas with a little IF(a3=0,"",g3/a3) in front of his straight g3/a3 and he gives you a b*****ing for taking so long. This usually comes in the form "It didn't take me that long to write the complete sheet!"
I feel your pain Dave.
Speadsheet work can be under-appreciated & everyone thinks they are a cell-master.
This seems appropriate:
http://www.unitedmedia.com/com…/dilbert2003261350731.gif 
Try clicking "Data-Text To Coulmns" to parse it out. If you are lucky your input data will be in fixed width columns.
