"Jack" <replyTo@newsgroup> wrote in message
news:OqIH0EdKGHA.3960@TK2MSFTNGP09.phx.gbl...
> Hello,
>          My code can read from Excel spreadsheet.
> What should be done to be able to write back to it?
> Your help appreciated,
> Jack
>

"Veign" <NOSPAMinveign@veign.com> wrote in message
news:%23tlEiIdKGHA.1124@TK2MSFTNGP10.phx.gbl...
> Like your read code except opposite.
>
> Seriously, how are you connecting to Excel to read the file?  Automation /
> ADO with SQL?
>
> --
> Chris Hanscom - Microsoft MVP (VB)
> Veign's Resource Center
> http://www.veign.com/vrc_main.asp
> Veign's Blog
> http://www.veign.com/blog
> --
>
>
> "Jack" <replyTo@newsgroup> wrote in message
> news:OqIH0EdKGHA.3960@TK2MSFTNGP09.phx.gbl...
>> Hello,
>>          My code can read from Excel spreadsheet.
>> What should be done to be able to write back to it?
>> Your help appreciated,
>> Jack
>>
>
>

> "Jack" <replyTo@newsgroup> wrote in message
> news:e6PyIddKGHA.216@TK2MSFTNGP15.phx.gbl...
>
>>Automation, I think.
>>Set oExcel = GetObject(, "Excel.Application") ' get existing Excel Object
>>Set oExcelWS = oExcel.ActiveWorkbook.ActiveSheet
>>
>>to read I do:
>>
>>oExcel.ActiveCell.Value
>>
>>Now I would like to write to another cell (not active) but with the
>>specified row and column.
>
>
>
> You need to consult Excel's documentation on its object library. It'll
> explain (at least describe) what you need to know.
>

"Gman" <nah> wrote in message news:eOtCLPeKGHA.740@TK2MSFTNGP12.phx.gbl...
> Just to add to Mike's post, bear in mind that while you *can* read and
> write using ActiveCell.Value it isn't necessary i.e. you don't have to
> select or activate a cell to read it.
>
> So using something like:
>
> Set oExcelWS = oExcel.ActiveWorkbook.Sheets("MyData")
> With oExcelWS
>    myVariable1 = .Cells(1,1).Value  'A1
>    myVariable2 = .Cells(1,2).Value  'B1
>    .Cells(2,1).Value = "I am cell A2"
>    .Cells(2,2).Value ="I am cell B2"
> End with
>
> is a lot more efficient than selecting and reading activecell.
>
> Far more efficient further is using variant arrays if you need to get a
> lot of data from a worksheet. For example, if you needed to get ALL of the
> data from a worksheet something like:
>
> Dim myData as Variant
> myData = oExcelWS.UsedRange
>  or
> myData = Range(oExcelWS.Cells(1,1),oExcelWS.Cells(10,2))
> will get a specified range.
>
> This load all the data into a 1-based variant array. This is
> (substantially) quicker than reading cell by cell.
>
> Likewise, you can dump data back (even a 0-based array) in one fell swoop.
> Again - it's sooooo much quicker. Air code for 0-based 2d array:
> Range(oExcelWS.Cells(1,1), _
>       oExcelWS.Cells(ubound(myData,1)+1,ubound(myData,2)+1)) _
>          = myData
>
>
>
> MikeD wrote:
>> "Jack" <replyTo@newsgroup> wrote in message
>> news:e6PyIddKGHA.216@TK2MSFTNGP15.phx.gbl...
>>
>>>Automation, I think.
>>>Set oExcel = GetObject(, "Excel.Application") ' get existing Excel Object
>>>Set oExcelWS = oExcel.ActiveWorkbook.ActiveSheet
>>>
>>>to read I do:
>>>
>>>oExcel.ActiveCell.Value
>>>
>>>Now I would like to write to another cell (not active) but with the
>>>specified row and column.
>>
>>
>>
>> You need to consult Excel's documentation on its object library. It'll
>> explain (at least describe) what you need to know.
>>

> Sorry to butt into this message thread, but I was just about to post a
> question asking for the fastest way to load all the data from a worksheet
> (great timing).
>
> I tried Gman's suggestion:
> Dim myData as Variant
> myData = oExcelWS.UsedRange
>
> and it does seem alot faster than loading the data cell by cell. But, when I
> tried to check the contents of MyData, I did not have any success.
>
> If  I check Ubound(Mydata), it shows 100 elements. But, if I try to display
> the data in a text box, I get a subscribt out of range error:
> Text1.Text = MyData(10)     <-- this will fail with the subscript out of
> range error.
>
> I feel that I am missing something obvious here, but am not sure what.
>
> Thanks
>
> "Gman" <nah> wrote in message news:eOtCLPeKGHA.740@TK2MSFTNGP12.phx.gbl...
>
>>Just to add to Mike's post, bear in mind that while you *can* read and
>>write using ActiveCell.Value it isn't necessary i.e. you don't have to
>>select or activate a cell to read it.
>>
>>So using something like:
>>
>>Set oExcelWS = oExcel.ActiveWorkbook.Sheets("MyData")
>>With oExcelWS
>>   myVariable1 = .Cells(1,1).Value  'A1
>>   myVariable2 = .Cells(1,2).Value  'B1
>>   .Cells(2,1).Value = "I am cell A2"
>>   .Cells(2,2).Value ="I am cell B2"
>>End with
>>
>>is a lot more efficient than selecting and reading activecell.
>>
>>Far more efficient further is using variant arrays if you need to get a
>>lot of data from a worksheet. For example, if you needed to get ALL of the
>>data from a worksheet something like:
>>
>>Dim myData as Variant
>>myData = oExcelWS.UsedRange
>> or
>>myData = Range(oExcelWS.Cells(1,1),oExcelWS.Cells(10,2))
>>will get a specified range.
>>
>>This load all the data into a 1-based variant array. This is
>>(substantially) quicker than reading cell by cell.
>>
>>Likewise, you can dump data back (even a 0-based array) in one fell swoop.
>>Again - it's sooooo much quicker. Air code for 0-based 2d array:
>>Range(oExcelWS.Cells(1,1), _
>>      oExcelWS.Cells(ubound(myData,1)+1,ubound(myData,2)+1)) _
>>         = myData
>>
>>
>>
>>MikeD wrote:
>>
>>>"Jack" <replyTo@newsgroup> wrote in message
>>>news:e6PyIddKGHA.216@TK2MSFTNGP15.phx.gbl...
>>>
>>>
>>>>Automation, I think.
>>>>Set oExcel = GetObject(, "Excel.Application") ' get existing Excel Object
>>>>Set oExcelWS = oExcel.ActiveWorkbook.ActiveSheet
>>>>
>>>>to read I do:
>>>>
>>>>oExcel.ActiveCell.Value
>>>>
>>>>Now I would like to write to another cell (not active) but with the
>>>>specified row and column.
>>>
>>>
>>>
>>>You need to consult Excel's documentation on its object library. It'll
>>>explain (at least describe) what you need to know.
>>>
>
>
>

"Gman" <nah> wrote in message news:ueO1VlsKGHA.3352@TK2MSFTNGP12.phx.gbl...
> Indeed sir, it is obvious. :-)
>
> Even if you load in one column, it's still a 2D array. Thus, if you want
> the first element you address it as myData(1,1).
>
> Example (air code):
>
>    with olXLws
>      'load in first 20 cells in Column A
>       myData1 = .Range(.cells(1,1),.cells(20,1))
>
>      'load in all "used" cells in column A - will include empty
>       myData2 = .Range(.cells(1,1), _
>                    .cells(.usedrange.rows.count,1))
>     end with
>
>    'to access cell A10 just use
>    Text1.Text = mydata1(10,1)
>    Text1.Text = mydata2(10,1)
>
> Likewise for *just* column B
>    myDataB = .Range(.cells(1,2),.cells(20,2))
>    Text1.Text = mydataB(10,1)
>
> i.e. it's still '1' for second dimension - not 2 - you're still in VB!
>
> HTH
>
> Jonny wrote:
>> Sorry to butt into this message thread, but I was just about to post a
>> question asking for the fastest way to load all the data from a worksheet
>> (great timing).
>>
>> I tried Gman's suggestion:
>> Dim myData as Variant
>> myData = oExcelWS.UsedRange
>>
>> and it does seem alot faster than loading the data cell by cell. But,
>> when I tried to check the contents of MyData, I did not have any success.
>>
>> If  I check Ubound(Mydata), it shows 100 elements. But, if I try to
>> display the data in a text box, I get a subscribt out of range error:
>> Text1.Text = MyData(10)     <-- this will fail with the subscript out of
>> range error.
>>
>> I feel that I am missing something obvious here, but am not sure what.
>>
>> Thanks
>>
>> "Gman" <nah> wrote in message
>> news:eOtCLPeKGHA.740@TK2MSFTNGP12.phx.gbl...
>>
>>>Just to add to Mike's post, bear in mind that while you *can* read and
>>>write using ActiveCell.Value it isn't necessary i.e. you don't have to
>>>select or activate a cell to read it.
>>>
>>>So using something like:
>>>
>>>Set oExcelWS = oExcel.ActiveWorkbook.Sheets("MyData")
>>>With oExcelWS
>>>   myVariable1 = .Cells(1,1).Value  'A1
>>>   myVariable2 = .Cells(1,2).Value  'B1
>>>   .Cells(2,1).Value = "I am cell A2"
>>>   .Cells(2,2).Value ="I am cell B2"
>>>End with
>>>
>>>is a lot more efficient than selecting and reading activecell.
>>>
>>>Far more efficient further is using variant arrays if you need to get a
>>>lot of data from a worksheet. For example, if you needed to get ALL of
>>>the data from a worksheet something like:
>>>
>>>Dim myData as Variant
>>>myData = oExcelWS.UsedRange
>>> or
>>>myData = Range(oExcelWS.Cells(1,1),oExcelWS.Cells(10,2))
>>>will get a specified range.
>>>
>>>This load all the data into a 1-based variant array. This is
>>>(substantially) quicker than reading cell by cell.
>>>
>>>Likewise, you can dump data back (even a 0-based array) in one fell
>>>swoop. Again - it's sooooo much quicker. Air code for 0-based 2d array:
>>>Range(oExcelWS.Cells(1,1), _
>>>      oExcelWS.Cells(ubound(myData,1)+1,ubound(myData,2)+1)) _
>>>         = myData
>>>
>>>
>>>
>>>MikeD wrote:
>>>
>>>>"Jack" <replyTo@newsgroup> wrote in message
>>>>news:e6PyIddKGHA.216@TK2MSFTNGP15.phx.gbl...
>>>>
>>>>
>>>>>Automation, I think.
>>>>>Set oExcel = GetObject(, "Excel.Application") ' get existing Excel
>>>>>Object
>>>>>Set oExcelWS = oExcel.ActiveWorkbook.ActiveSheet
>>>>>
>>>>>to read I do:
>>>>>
>>>>>oExcel.ActiveCell.Value
>>>>>
>>>>>Now I would like to write to another cell (not active) but with the
>>>>>specified row and column.
>>>>
>>>>
>>>>
>>>>You need to consult Excel's documentation on its object library. It'll
>>>>explain (at least describe) what you need to know.
>>>>
>>
>>