Posts by Cyberdude

Re: Read Text From A Closed Workbook

I use the function “ExecuteExcel4Macro” in a fashion similar to the following:

Sub GetData
Dim Path As String
Path = "'C:\Excel Documents\[Raw Data.xls]DataSheetNm'!"
Range("A1").Value = ExecuteExcel4Macro(Path & "DefinedNm")
End Sub

"DefinedNm" is a defined name I have given to the cell that I'm extracting data from in the closed workbook.

Re: Defining A Variable As A Range

Something like this maybe:

Dim N As Long,RefVal As ?
RefVal= Sheets(middle).Range("E3")
With Worksheets(later).Range("A6")
For N = 0 To 5
    If .Offset(N, 0)  = RefVal Then Exit For 
Next N
End With

Re: Adding Worksheets If Data Present

Yes, you can write an macro to detect when data is present on a line, then create a new sheet. You'll probably want to put it into an event macro. I would need more details about what you are doing. For inistance, it occurs to me that you will probably have a VERY large number of sheets in that workbook after a short time.

Re: Macro To Copy Data To Another Workbook

I may stand corrected, but I believe that you will have to open the workbook in order to put data into it in a real time manner. I'm curious ... why are you so against opening the target workbook? You can open it automatically when you open the source workbook. Use the Workbook_Open event macro to do it.
You can also use an event macro to detect when you enter new data on the source workbook and execute a macro to do the copying for you.

Re: Open Workbook, Run Macro, Save - Automate

Good show, Frankie. Glad to help.
Sid

Re: Open Workbook, Run Macro, Save - Automate

Is this the sort of thing you had in mind?

Dim ThisWkbkNm As String
ThisWkbkNm = ActiveWorkbook.Name
. . .
'Open/Close "WorkbookA.xls"
Workbooks.Open Filename:="C:\Excel Documents\WorkbookA.xls"  
Application.Run "'WorkbookA.xls'!MyMacroNameA"
On Error Resume Next
Workbooks("WorkbookA.xls").Close SaveChanges:=True    
     
'Open & Close next workbk
Workbooks.Open Filename:="C:\Excel Documents\WorkbookB.xls"  
Application.Run "'WorkbookB.xls'!MyMacroNameB"
On Error Resume Next
Workbooks("WorkbookB.xls").Close SaveChanges:=True  


Workbooks(ThisWkbkNm).Activate                             'Reactivate this workbook
. . .

Re: Numeric to Text Conversion

I don't know if you can make good use of it, but when you enter a number into a cell, precede it with a single quote mark. That'll make it text. (e.g. '1234.56)

Re: Extract Data From Many Workbooks For Calculations

Hey, FWC, here's a technique for extracting data values from a closed workbook, in case you are unfamiliar with it.

Sub Excel4MacroTest()
Dim Path As String, RemoteValue As String
'Path = "'C:\path to workbook\[TargetWorkbookName.xls]TargetSheetName'!"
Path = "'C:\Excel Documents\[My Investments.xls]Stocks'!"
'Statement format using a cell address:
RemoteValue = ExecuteExcel4Macro(Path & Range("A1").Address(ReferenceStyle:=xlR1C1))
'Statement format using a cell with a defined name:
RemoteValue = [color=red]ExecuteExcel4Macro[/color](Path & "TgtCell1")
MsgBox RemoteValue
End Sub

I'm no expert at using "ExecuteExcel4Macro" (see VBA Help), but I DO use it a lot to advantage.
The easiest way to use it is to define a name for each target cell in its resident workbook. That simplifies writing the address. But you can also use the cell's address ("A1") as shown in the example.
Note! When writing the path string begin it with one double quote followed by one single quote: "'.
I don't know if you can use it, but at least this gives you an additional tool to stare at.
Sid

Re: auto increment on open

Hey, Kim, it seems to me your requirement is likely to create some invoice numbers that will never be used if someone opens the workbook then decides to close it without doing anything. Is that a potential problem??
Just wondering.
Seems like it might be better to open the workbook, then IF you want a new invoice number, press a button that calls a macro.
Or you could even prompt the user and ask if he/she wants a new invoice number before continuing.

Re: Making a chart only to look at cells not equal to null

Another technique is to substitute a N/A error for the null value. You can create an artificial N/A value by using the function "NA()" in an IF statement that sets the cell value. Charts don't plot N/A errors.

Re: Empty rows should be hided through code !

Chock, I don't fully understand what is happening. How is the row being examined for being empty or non-empty? Do you just look at it? If so, I assume you would like to click on the empty row to select it then press a "Hide Row" button.
Same for the unhide process, except you can't click on a hidden row. To unhide a row manually, you have to select the visible rows above and below the hidden row.
If you want to use VBA to examine the input rows,then code can be written to select the rows of interest and hide or unhide as is appropriate.
I'm not sure how you receive the sheet. Do you get a printed version or is the sheet transmitted to you electronically in some fashion?

Re: Collect Time via InputBox Code

Sure. Just change "NOW" to FORMAT(TIME,"HH:MM")
Sid

Re: Reducing the amount of IF's needed.

I'm not sure I have the picture, but can you construct a SELECT CASE to speed up your comparisons:

Select Case cboname
    Case Range("E2"):   txtpay = Range("F2")
    Case Range("E3"):   txtpay = Range("F3")
    Case Range("E4"):   txtpay = Range("F4")
     '(rest of table entries)
End Select

Re: inputbox and times

Here's a possible alternative that takes into account that there can be 1 or 2 digits preceding the decimal point:

Sub getTime()
    Dim str    As String   
    str = Replace(Application.InputBox("Please enter a time, use format HH:MM"), ".", ":")
    MsgBox str
End Sub

Re: Time between two dates

Just a comment, in general, when you want the time between two dates, the result should be formatted with brackets: [hh]:mm

Re: 24 hour time

Here's a little function for you to play with:

Function TimeDiff(BegTim As Date, EndTim As Date)        
Application.Volatile (False)  
'=IF(LaterTime - EarlierTime >0, LaterTime - EarlierTime, 1+LaterTime - EarlierTime)
If EndTim - BegTim > 0 _
     Then TimeDiff = Format(EndTim - BegTim, "h:mm") _
      Else TimeDiff = Format(1 + EndTim - BegTim, "h:mm")
End Function

The cell whch gets the result should probably be formatted as "[h]:mm". This is a sample calling sequence:

Call TimeDiff("8:00 AM", "6:00 PM")

Re: if selection = string or value

Remember that testing for "blank" is not the same as testing for null. The following two statements can produce different results:
[VBA]If X = "" Then ...
If X = " " Then ...[/VBA]
Also when testing for null (""), then it is more efficient to use "vbNullString":
[VBA]If X = "" Then ... 'Not preferred
If X = vbNullString Then ... 'Preferred form[/VBA]

Re: Newbie: Replace text in fields

You might also look at Case logic, which is quite handy for translation type tasks. Assume that variable "X" contains the value to be tested and replaced, then:
[VBA]Select Case X
Case 2: X = "7.15 - 19.00"
Case 4: X = "00:00-8:00"
Case Else: ....
End Select[/VBA]

Re: Calculations do not make sense.

Be absolutely sure that the two numbers you are subtracting have the values you are seeing. For example you said you enter the number 97.4. If you typed in that number, then what you see is what you have, but if that number is the result of another arithmetic operation, what you see (97.4) might in fact be something like 97.40000632. That can throw off subsequent calculations. You can test any number by formatting it with a LOT of fractional digits just to see if you have a lot of 0's following the (for example) 4 in 97.4. It's easy to pick up fractional digits when what you are doinf is dependent on other calculations. As previous post have indicated, by rounding each value you can guarantee that what you see is what you get.

Re: start up message

Hey, if you really want to get their attention, use the text-to-speech facility:

Application.Speech.Speak ("Only enter data in the green cells, for more information please see Jeff")

instead of Msgbox.