Importing data from VB6 to Excel.. 3

[thetambarineman]

Hello everone,

I want to take data from some forms within my project - and place it into certain cells in excel..
i.e. each time i want the data to go beneath the existing data..

Heres what ive got so far..


'open the app
dim xcel as new excel.application
'make it viz
xcel.visible = true
'open the workbook
xcel.Workbooks.Open ("N:\Work.xls&quot

The workbook already has cell values along the top..
i.e. forename, surname, supervisor etc..

Range("a1:g1&quot.select
range("g1&quot.activate
selection.font.bold = true
selection.columns.autofit


'now i want the data thats coming off the forms to go into the cells below the current data. Each time the excel starts the new data should go into the line below...


Haven't explained this well but any help is appreciated...

Paul

 

[swilliams]

Create a loop to go from Range("A2&quot down. Check the contents of the cell and when it is empty, put your data in the row. (You may want to check that all columns are empty before inserting the data, to prevent overwriting anything).

Simon

 

[thetambarineman]

Thanks-can you be a bit more specific with the code involved for such a procedure- i've been driving all day-and its making me lazy!!

Thanks in advance...

Paul..

 

[swilliams]

Hi Paul, try this:

Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim lRow As Long
Dim lCol As Long
Dim bCanInsert As Boolean

' variables holding data
Dim sCol1 As String
Dim sCol2 As String
Dim sCol3 As String
Dim sCol4 As String
Dim sCol5 As String
sCol1 = "Col1 data"
sCol2 = "Col2 data"
sCol3 = "Col3 data"
sCol4 = "Col4 data"
sCol5 = "Col5 data"

Const lStartRow As Long = 2
Const lMaxRows As Long = 65536 ' maximum columns in Excel97 and Excel2000
Const lMaxCols As Integer = 5 ' assuming that there are 5 columns of data

Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\aa\book1.xls&quot
Set ws = wb.Worksheets(1)
For lRow = lStartRow To lMaxRows
bCanInsert = True
For lCol = 1 To lMaxCols
If Len(ws.Cells(lRow, lCol) & &quot;&quot <> 0 Then
bCanInsert = False
Exit For
End If
Next lCol
If bCanInsert = True Then
ws.Cells(lRow, 1) = sCol1
ws.Cells(lRow, 2) = sCol2
ws.Cells(lRow, 3) = sCol3
ws.Cells(lRow, 4) = sCol4
ws.Cells(lRow, 5) = sCol5
Exit For
End If
Next lRow
Set ws = Nothing
wb.Save
wb.Close
Set wb = Nothing
Set xlApp = Nothing


PS - Did you know that you have a lot of space in you auto signature after your name??

Simon

 

[thetambarineman]

Thanks so much-that was a gr8 help...

 

[ratman]

Hi,

To paste data from your form to the next empty cell in a column can be done in 4 lines.
Copy your data then

Pick the one line below depending on if the destination file is already open or not:


Workbooks.Open Filename:=&quot;O:\your_excel_file_here.xls&quot; ' use if not open yet
Workbooks(&quot;your_excel_file_here.xls&quot.activate ' use if open but not active
Sheets(&quot;Sheet2&quot.Select ' use if open and active but wrong sheet


Then line 2 is:

mylastrow = [D2].End(xlDown).Row 'assumes col D as your destination and just in case D1 is a header and it's empty


Then line 3 is:

Range (&quot;$D$&quot; & mylastrow).Select


Then line 4 is one of these:

Selection.Paste 'or ActiveSheet.Paste or ActiveSheet.Paste Destination:=[D & mylastrow]....if the paste destination works you can skip the Range Select line above