Capture userform data and transfer to Excel

[oglezsa]

Hola!!!, Its me again first and foremost want to thank "Lucas" and "fumei" for all te help on all my past questions big THANKS!!!! :bow: .... now here is where i need help again ...I have a team that is responible of sending letters to our customers so i was looking for a way to Copy the data the user enters in the userform (Textbox, checkbox, Comobox...etc) and copy it to an Excel spreadsheet, the reason is to keep a log/record of the letters we send out and what selections they had made and also what they have type (Name, last name, address...etc) is this posible with userforms, if so how?. thanks again!!!

[fumei]

"so i was looking for a way to Copy the data the user enters in the userform (Textbox, checkbox, Comobox...etc) and copy it to an Excel spreadsheet"

That is way too general. The short answer is:

1. have an instance of Excel, and have it either open an existing file, or create a new file;

2. grab the data from whatever control on the userform;

3. put that data into the Excel file.

Here is a VERY simple chunk of code.

ASSUMPTIONS:

1. the IS an existing file (c:\zzz\Test\ExistingFile.xls)

2. you DO have a reference to Excel (for early-binding).

OK?

So, a simple userform (in Word). It has two commandbuttons - cmdIntoExcel, and cmdFinishUp. It has one textbox (txtExcelCrap).

Here is the entire code. Obviously you will have to adjust for your purposes, but this can show you how it is basically done.

Option Explicit
Public appXL As Excel.Application
Public xlFile As Excel.Workbook

Private Sub UserForm_Initialize()
Set appXL = CreateObject("Excel.Application")
Set xlFile = appXL.Workbooks.Open _
(FileName:="c:\zzz\Test\ExistingFile.xls")
End Sub

Private Sub cmdIntoExcel_Click()
xlFile.Worksheets("Sheet1").Range("A2").Value = txtExcelCrap.Text
appXL.Visible = True
End Sub

Private Sub cmdFinishUp_Click()
xlFile.Close
Set xlFile = Nothing
appXL.Quit
Set appXL = Nothing
End Sub



What is going on:

1. Two Public variables, the Excel application instance, and the Excel file object. They are Public because they are being used in more than one procedure.
Option Explicit
Public appXL As Excel.Application
Public xlFile As Excel.Workbook


2. Initializing the userform SETS the public variable of the Excel instance, and uses that instance to SET the Excel file object to the file c:\zzz\Test\ExistingFile
Private Sub UserForm_Initialize()
Set appXL = CreateObject("Excel.Application")
Set xlFile = appXL.Workbooks.Open _
(FileName:="c:\zzz\Test\ExistingFile.xls")
End Sub


3. clicking the commandbutton cmdIntoExcel takes the value of the textbox txtExcelCrap, and puts it into the cell A2 of the Excel file object.
Private Sub cmdIntoExcel_Click()
xlFile.Worksheets("Sheet1").Range("A2").Value = txtExcelCrap.Text
appXL.Visible = True
End Sub
IMPORTANT! IMPORTANT! There is NO error trapping in this example. If txtExcelCrap is blank (empty) then that is what goes over to the cell.

4. the second commandbutton cleans up. This is VERY VERY important. By cleaning up I mean:
- the Excel file is properly closed (it is not saved in this example, and you probably want to do that!);
- then the Excel file object is properly destroyed;
- the Excel application instance is properly closed (Quit);
- the Excel instance object is properly destroyed.

Private Sub cmdFinishUp_Click()
xlFile.Close
Set xlFile = Nothing
appXL.Quit
Set appXL = Nothing
End Sub

The order this is done is important. It is VERY important that you understand the difference between the application itself, and the memory the object has been allocated.

Final note: the value taken from the textbox is a string - NOT a number. As Excel is a number-based application, you may (or may not) want to make the value going into the Excel cell explicitly a number. This is up to you, depending on what this information is, and what it is going to be used for in Excel.

[lucas]

It's very possible.

Questions:

Is your userform in Word?

Does it put data into the word document?


If so, let's see it.

It can probably be modified to do both at the same time.

Attach it by clicking go advanced and scroll down to find manage attachments.

[oglezsa]

Lucas, yes and yes to both questions the user form is in word and also puts the information in the document. The letter and the code you already have, i send it to you on the last topic, I can resend it if you want me to.

[oglezsa]

Lucas i just send it to you...

[oglezsa]

Fumei, Its giving me an error when i try to compile it...user defined type not defined. and is highligthing ...appXL As Excel.Application

[lucas]

I've visited 30 topics ogleza.. You should attach it where ever you are asking a question so we don't have to hunt it.

You're error in post # 6 is probably because you need to add a reference in vbe to excel. Tools - reference.

[lucas]

I plugged in Gerry(fumie)'s code and fixed on textbox only to show you how this works.

The excel file must exist in the path in the code or it will fail. No error checking added so far.

I added the two public variables at the top of the userform code.

I added the workbook open code to the end of the initialize procedure which contains the path where an excel file named Book1.xls must exist or change that part of the code.

I added the code to copy textbox 1 value to range b2 of sheet 1 in the excel file and close the excel file.

This should get you started.

I have tested it and it does work. It is just set up to copy one textbox at this time. You will have to add the rest.

[oglezsa]

Thanks So much' i'll try it this afternoon from another computer and location since my Job Firewall does no let me pull, open, unzip or download anything.

[white_flag]

cool explanation on #2.

[fumei]

Re: #6: "Fumei, Its giving me an error when i try to compile it...user defined type not defined. and is highligthing ...appXL As Excel.Application"

Please read what I posted as #2, below.

ASSUMPTIONS:

1. the IS an existing file (c:\zzz\Test\ExistingFile.xls)

2. you DO have a reference to Excel (for early-binding).