VB 6.0 - Getting data into Excel - Asked By Joe Lewis on 16-May-12 07:30 PM

I wrote a small VB program that writes a simple text file 1000 lines long, and each line has 50 numbers (or words) separated by commas.  I then have an Excel spreadsheet that I put this data into.  I go into a particular "data input" sheet, in cell A3 let's say, and I do the Data - Get Data stuff.  When the window about the "tab" vs. "comma" delimiting comes up, I switch it from tab to comma.  When I finish with all that, the data populates 50 columns (each with 1000 rows) on that sheet beautifully.  My other sheets each have a dedicated column that reads the corresponding column on this "data input sheet".

My first question may best be asked on the Excel forum, but I want to know if there's a way to change all the default radio buttons in that Data-Get Data process?  Like "Save Query Definition",  comma delimited vs. tab delimited, and a couple other things.  I want my master spreadsheet to bring up the proper defaults.

More importantly, back in my little VB6 program (which created the 1000 line text file), I would REALLY like to add some code that goes ahead and puts that text file into the "data input sheet" of the Excel spreadsheet (to replace the manual stuff I described above).  Would this be that difficult to do (I'm hoping it is relatively easy, or at least quite possible)?  Can anyone help me with this?   Thanks a lot.
wally eye replied to Joe Lewis on 17-May-12 10:47 PM
Have you tried using the object model to push it to Excel?  Something like

public sub PushToExcel(byval strPath as string, strWorksheet as string, strCell as string)

Dim newExcelApp As Excel.Application

Dim newWbk As Excel.Workbook

Dim newWkSheet As Excel.Worksheet

Set newExcelApp = Excel.Application

Set newWbk = newExcelApp.Workbooks.open(strpath)

Set newWkSheet = newWbk.Worksheets(strworksheet)

newwksheet.Range(strcell).resize(ubound(arrData), ubound(arrData,2)) = arrData

newwbk.save false

set newwksheet = nothing
set newwbk = nothing
set newexcelapp = nothing

end sub

I'm doing this without testing, so there might be some errors.  It assumes you have an array of data to push to a spreadsheet, 1000 rows by 50 columns should easily be handled by the routine.

wally eye replied to wally eye on 18-May-12 10:16 AM
And yes, I forgot to mention you need to set a reference to Excel (Tools, References from the VBA window)