> How would I make a VB6 .exe file that can manipulate data in a running
> version of Excel?
> I am thinking of something like this:
>
> Sub MakeNumbers()
>
>     Dim xlApp As Excel.Application
>     Dim arr
>     Dim i As Long
>     Dim c As Long
>
>     Set xlApp = GetObject(, "Excel.Application")
>
>     On Error Resume Next
>
>     arr = xlApp.ActiveWindow.RangeSelection
>
>     For i = 1 To UBound(arr)
>         For c = 1 To UBound(arr, 2)
>             If Not IsNumeric(arr(i, c)) And _
>                IsDate(arr(i, c)) Then
>                 arr(i, c) = Val(arr(i, c))
>             End If
>         Next
>     Next
>
>     xlApp.ActiveWindow.RangeSelection = arr
>
>     Set xlApp = Nothing
>
> End Sub
>
>
> I have set the reference to Excel in the VB Project, but running this from
a
> simple VB form doesn't work.
> The data doesn't change and the .exe crashes.
> Obviously, I am making some fundamental mistakes here.
> Ideally, the VB form should be modeless, but that is of later concern.
> Thanks for any assistance.
>
> RBS
>
>

"Jim Edgar @cox.net>" <djedgar<removethis> wrote in message
news:O77KfdxYFHA.1344@TK2MSFTNGP15.phx.gbl...
>> How would I make a VB6 .exe file that can manipulate data in a running
>> version of Excel?
>> I am thinking of something like this:
>>
>> Sub MakeNumbers()
>>
>>     Dim xlApp As Excel.Application
>>     Dim arr
>>     Dim i As Long
>>     Dim c As Long
>>
>>     Set xlApp = GetObject(, "Excel.Application")
>>
>>     On Error Resume Next
>>
>>     arr = xlApp.ActiveWindow.RangeSelection
>>
>>     For i = 1 To UBound(arr)
>>         For c = 1 To UBound(arr, 2)
>>             If Not IsNumeric(arr(i, c)) And _
>>                IsDate(arr(i, c)) Then
>>                 arr(i, c) = Val(arr(i, c))
>>             End If
>>         Next
>>     Next
>>
>>     xlApp.ActiveWindow.RangeSelection = arr
>>
>>     Set xlApp = Nothing
>>
>> End Sub
>>
>>
>> I have set the reference to Excel in the VB Project, but running this
>> from
> a
>> simple VB form doesn't work.
>> The data doesn't change and the .exe crashes.
>> Obviously, I am making some fundamental mistakes here.
>> Ideally, the VB form should be modeless, but that is of later concern.
>> Thanks for any assistance.
>>
>> RBS
>>
>>
>
> Fortunately, the VB help file uses Excel as it's example of the GetObject
> method.  Try searching your help file for GetObject and then check out the
> example.  If you have further questions then please post them.
>
> Jim Edgar
>
>

"Jim Edgar @cox.net>" <djedgar<removethis> wrote in message
news:eJBGhu5YFHA.1964@TK2MSFTNGP10.phx.gbl...
>
> "RB Smissaert" <bartsmissa***@blueyonder.co.uk> wrote in message
> news:%23EffgtyYFHA.3320@TK2MSFTNGP12.phx.gbl...
>> I got GetObject(, "Excel.Application") from the help, but couldn't find
>> anything else. Maybe I looked in the wrong help.
>>
>> RBS
>
> Sorry,  I meant CreateObject.  Anyway, here's some code to look at.
> Just start an instance of Excel and run this.
>
> Option Explicit
>
> Private Sub Form_Load()
>    MakeNumbers
> End Sub
>
> Sub MakeNumbers()
>
>    Dim xlApp As Excel.Application
>    Dim xlSht As Excel.Worksheet
>    Dim arr
>    Dim i As Long
>    Dim c As Long
>
>    Set xlApp = GetObject(, "Excel.Application")
>
>    On Error Resume Next
>
>    xlApp.Visible = True
>    Set xlSht = xlApp.ActiveSheet
>    ' Create some data
>    For i = 1 To 5
>        For c = 1 To 5
>            xlSht.Cells(i, c) = i * c
>        Next
>    Next
>    ' Copy and paste the data
>    arr = xlSht.Range("a1", "e5").Value
>    xlSht.Range("f1", "j5").Value = arr
>
>    Set xlApp = Nothing
>
> End Sub
>
> Jim Edgar
>
>

"Rick Rothstein" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in message
news:uvxVjesZFHA.1148@tk2msftngp13.phx.gbl...
>
> "RB Smissaert" <bartsmissa***@blueyonder.co.uk> wrote in message
> news:egfA3WsZFHA.3784@TK2MSFTNGP12.phx.gbl...
>> Thanks, I got this working now.
>> Just changing it to:
>>
>>      For i = 1 To UBound(arr)
>>         For c = 1 To UBound(arr, 2)
>>             If IsNumeric(arr(i, c)) And _
>>                Not IsDate(arr(i, c)) Then
>>                 arr(i, c) = Val(arr(i, c))
>>             End If
>>         Next
>>     Next
>
> You might not want to rely heavily on the IsNumeric function. Consider
> this from a previous post of mine...
>
> I usually try and steer people away from using IsNumeric to "proof"
> supposedly numeric text. Consider this (also see note at end of post):
>
>    ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)")
>
> Most people would not expect THAT to return True. IsNumeric has some
> "flaws" in what it considers a proper number and what most programmers
> are looking for.
>
> I had a short tip published by Pinnacle Publishing in their Visual Basic
> Developer magazine that covered some of these flaws. Originally, the tip
> was free to view but is now viewable only by subscribers.. Basically, it
> said that IsNumeric returned True for things like -- currency symbols
> being located in front or in back of the number as shown in my example
> (also applies to plus, minus and blanks too); numbers surrounded by
> parentheses as shown in my example (some people use these to mark
> negative numbers); numbers containing any number of commas before a
> decimal point as shown in my example; numbers in scientific notation (a
> number followed by an upper or lower case "D" or "E", followed by a
> number equal to or less than 305 -- the maximum power of 10 in VB); and
> Octal/Hexadecimal numbers (&H for Hexadecimal, &O or just & in front of
> the number for Octal).
>
> NOTE:
> ======
> In the above example and in the referenced tip, I refer to $ signs and
> commas and dots -- these were meant to refer to your currency, thousands
> separator and decimal point symbols as defined in your local settings --
> substitute your local regional symbols for these if appropriate.
>
> As for your question about checking numbers, here are two functions that
> I have posted in the past for similar questions..... one is for digits
> only and the other is for "regular" numbers:
>
>     Function IsDigitsOnly(Value As String) As Boolean
>         IsDigitsOnly = Len(Value) > 0 And _
>                        Not Value Like "*[!0-9]*"
>     End Function
>
>     Function IsNumber(ByVal Value As String) As Boolean
>         '   Leave the next statement out if you don't
>         '   want to provide for plus/minus signs
>         If Value Like "[+-]*" Then Value = Mid$(Value, 2)
>         IsNumber = Not Value Like "*[!0-9.]*" And _
>                           Not Value Like "*.*.*" And _
>                           Len(Value) > 0 And Value <> "." And _
>                           Value <> vbNullString
>     End Function
>
> Here are revisions to the above functions that deal with the local
> settings for decimal points (and thousand's separators) that are
> different than used in the US (this code works in the US too, of
> course).
>
>     Function IsNumber(ByVal Value As String) As Boolean
>       Dim DP As String
>       '   Get local setting for decimal point
>       DP = Format$(0, ".")
>       '   Leave the next statement out if you don't
>       '   want to provide for plus/minus signs
>       If Value Like "[+-]*" Then Value = Mid$(Value, 2)
>       IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _
>                  Not Value Like "*" & DP & "*" & DP & "*" And _
>                  Len(Value) > 0 And Value <> DP And _
>                  Value <> vbNullString
>     End Function
>
> I'm not as concerned by the rejection of entries that include one or
> more thousand's separators, but we can handle this if we don't insist on
> the thousand's separator being located in the correct positions (in
> other words, we'll allow the user to include them for their own
> purposes... we'll just tolerate their presence).
>
>     Function IsNumber(ByVal Value As String) As Boolean
>       Dim DP As String
>       Dim TS As String
>       '   Get local setting for decimal point
>       DP = Format$(0, ".")
>       '   Get local setting for thousand's separator
>       '   and eliminate them. Remove the next two lines
>       '   if you don't want your users being able to
>       '   type in the thousands separator at all.
>       TS = Mid$(Format$(1000, "#,###"), 2, 1)
>       Value = Replace$(Value, TS, "")
>       '   Leave the next statement out if you don't
>       '   want to provide for plus/minus signs
>       If Value Like "[+-]*" Then Value = Mid$(Value, 2)
>       IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _
>                  Not Value Like "*" & DP & "*" & DP & "*" And _
>                  Len(Value) > 0 And Value <> DP And _
>                  Value <> vbNullString
>     End Function
>
> Rick - MVP
>

"RB Smissaert" <bartsmissa***@blueyonder.co.uk> wrote in message
news:%23SLz0xwYFHA.3780@tk2msftngp13.phx.gbl...
> How would I make a VB6 .exe file that can manipulate data in a running
> version of Excel?
> I am thinking of something like this:
>
> Sub MakeNumbers()
>
>    Dim xlApp As Excel.Application
>    Dim arr
>    Dim i As Long
>    Dim c As Long
>
>    Set xlApp = GetObject(, "Excel.Application")
>
>    On Error Resume Next
>
>    arr = xlApp.ActiveWindow.RangeSelection
>
>    For i = 1 To UBound(arr)
>        For c = 1 To UBound(arr, 2)
>            If Not IsNumeric(arr(i, c)) And _
>               IsDate(arr(i, c)) Then
>                arr(i, c) = Val(arr(i, c))
>            End If
>        Next
>    Next
>
>    xlApp.ActiveWindow.RangeSelection = arr
>
>    Set xlApp = Nothing
>
> End Sub
>
>
> I have set the reference to Excel in the VB Project, but running this from
> a simple VB form doesn't work.
> The data doesn't change and the .exe crashes.
> Obviously, I am making some fundamental mistakes here.
> Ideally, the VB form should be modeless, but that is of later concern.
> Thanks for any assistance.
>
> RBS
>
>
>