[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sending data to excel spreadsheet

Posted on 2005-04-12
10
Medium Priority
?
140 Views
Last Modified: 2010-05-02
I know absolutely nothing about basic so this may sound like a stupid question.  I have an excel file already started (C:\\projects.xls) with 2 columns: Person and Project. If I have a form in Visual Basic with 2 edit boxes for Person and Project, how can I export the data from those edit boxes to that excel file? I'm not trying to create a new excel file, just add to the existing one?
0
Comment
Question by:cheapstr
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 300 total points
ID: 13769293
Good question.

In Project:References, set a reference to 'Microsoft Excel {whatever version} Object Library'

Then use code that goes something like this...

Dim xl As New Excel.Application
Dim xlw As Excel.Workbook

 xl.Workbooks.Open ("sThePathAndFile.xls")
       
'Write single values to named ranges in the .xls file  (You'll have to manually assign them using Insert..Names
xl.ActiveSheet.Range("branch_id_name").Value = Me.Person
xl.ActiveSheet.Range("division_id_name").Value = Me.Project

'or hard-code the cell reference
xl.Sheets("SheetName").Cells("A1").Value = Me.Person
xl.Sheets("SheetName").Cells("A1").Value = Me.Project
'you get the idea.

For Each xlw In xl.Workbooks
     xlw.Save
Next
xl.Quit

Hope this helps.
-Jim
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13769296
In the first comment, if you choose Named Fields in Excel, replace branch_id_name and division_id_name with whatever you choose to name them.
0
 

Author Comment

by:cheapstr
ID: 13769450
thanks for the quick response! I included the reference for excel 11.  I keep getting runtime error 1004: Application - defined or object - defined error for line:
             xl.ActiveSheet.Range("person").Value = Me.Person
any ideas?
thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 13769476
Couple of thoughts...

That requires that you have an named range in Excel named person
You have a textbox on your form named Person.  
Maybe xl.Sheets("NameTheSheetHere")...

also add this to the end of your code

Set xl = Nothing
Set xlw = Nothing
0
 
LVL 1

Expert Comment

by:virendra_malushte
ID: 13769668
This is how you NAME a RANGE in excel

Select The column Person in excel sheet..

GOTO INSERT > NAME> DEFINE
It will open a window Define Name..
Give a name "person" without quotes.
Click Okay..

So you have defined a name person to the selected range..

now the error should not come..

otherwise you can also write  
 xl.ActiveSheet.Range("A1:A10").Value = Me.Person
instead of person in the below code..
xl.ActiveSheet.Range("person").Value = Me.Person

where A is the column & 1/10 are rows that you require to search in..

regds



0
 

Author Comment

by:cheapstr
ID: 13769764
OK, I figured out that I had to assign the range, however; if i assign a range from say...A1 - A100, it will insert the value of my user form's text box in A1-A100. I only need to insert it once, in the next available cell. for example: say my excel file already has data in the 'person' column:
Person
----------
John Doe
Jane Doe

In my user form, I want to add another person...Walter Doe. What I need is to check down that column until I reach the first empty cell and add the new item in that cell, so I would end up with:
Person
----------
John Doe
Jane Doe
 Walter Doe

I hope this makes sense? I usually work with C++ and got all of this to work in that, but I am trying my hand at learning Visual Basic, which I know very little about. How to I just add this data to the end of the excel file?
0
 
LVL 4

Accepted Solution

by:
Gruff82 earned 300 total points
ID: 13770154
The way I would go about this, so as not to have to worry about having named ranges in the spread sheet would be to go about it like this.

set the type library in your project as mentioned by jimhorn

pass the filename of the spreadsheet to open as a parameter and in your form have a public variable iInsertCustomerRow and a public xl variable, this is so they can be accessed from multiple procedures \ functions

Code:

public iInsertCustomerRow as integer
public xl As New Excel.Application
Sub OpenPrejectSpreadSheet(sFilename as string)


' Open the workbook
xl.workbooks.open(sfilename)

' now presuming the first row in your columns are headers I'm going to start at 2 and there
' will be both a first name and a last name for each person.

' Select first cell under headers (if any), you can also change the 1 if you don't start at the first column

xl.cells(2,1).activate

do while lenb(trim$(activecell.value)) > 0
xl.cells(activecell.row + 1, 1).Activate

loop

iInsertCustomerRow = activecell.Row

End Sub

You could then have a AddCustomer Procedure for adding the customer
calling it from your for like

call AddCusttomer(txtFirstName.text, txtLastName.text, iInsertCustomerRow)

Sub AddCustomer(sFirstName As String, sLastName As String, iRow As String)

' Add First and Last names to the next customer row
cells(irow,1) = sfisrtname
cells(irow,1) = slastname

' Increase the iInsertCustomerRow to allow for the new customer thats been added
iInsertCustomerRow = iInsertCustomerRow + 1

End Sub

Hope this helps

Gruff
0
 
LVL 4

Expert Comment

by:Gruff82
ID: 13770181
Oh yeah sorry and one more thing you could do is add the following code to your form closing event, this will then give the user the option to save the changes or not.

' Check to see if there are changes in the workbook
if not xl.ActiveWorkbook.Saved then

' Ask the user if they want to save the changes or not.
  if MsgBox("Do you wish to save the changes?","Save Changes?",vbInformation + vbYesNo) = vbYes then
     xl.ActiveWorkbook.Save
  end if

end if

and that should do it.

Gruff
0
 
LVL 4

Expert Comment

by:Gruff82
ID: 13770194
Sorry must be to early in the morning, should be name and project not first name and last name like I have done but name and project, easily adapted though but in the add customer code I set the first name and last name (project) to the same field and should read :

xl.cells(iRow,1) = sFirstName
xl.Cells(iRow,2) = sLastName

sLastName being project in your case.

0
 

Author Comment

by:cheapstr
ID: 13777396
OK, between both of your answers I believe I managed to get this working. Only one more problem, after excel sheet is updated and closed, it still runs in task manager. Here is what I am doing.

Private Sub CommandButton1_Click()
Dim xl As Object
Dim iInsertCustomerRow As Integer
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("C:\\test.xls")

xl.Cells(2, 1).Activate
iInsertCustomerRow = 2
Do While LenB(Trim$(ActiveCell.Value)) > 0
xl.Cells(ActiveCell.Row + 1, 1).Activate
iInsertCustomerRow = ActiveCell.Row
Loop

 Cells(iInsertCustomerRow, 1) = UserForm1.Person
 iInsertCustomerRow = iInsertCustomerRow + 1
 
 xl.ActiveWorkbook.Save
 xl.ActiveWorkbook.Close
 xl.Workbooks.Close  
 xl.Quit  
 Set xl = Nothing  
 UserForm1.Hide

End Sub

The excel.exe still runs in the task manager until I either manually kill it, or exit my VB application. Is there something that I am missing?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question