[ms office - 2003] How to export data that you insert in a VB Form into a spreadsheet

[EaglesNerd]

Hi

 

I have created a form in VB and want people to be able to input information into the form and for it to then copy into the spreadsheet. Does anyone know how to do this ?

 

Thanks

 

Eaglesnerd

[Steve21]

Any chance you could post more info on what you want copied? or just want a basic outline?

 

Steve

[LosOjos]

Are we talking about Visual Basic (VB) or Visual Basic for Applications (VBA)?

 

If you've developed the form in VBA in Excel and want to add the data entered to the relevant spreadsheet within the workbook containing your VBA project, that shouldn't be too difficult, let us know.

[Steve21]

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

	Dim ExcelObj As Object
	Dim BookObj As Object
	Dim SheetObj As Object
	ExcelObj = CreateObject("Excel.Application")
	BookObj = ExcelObj.Workbooks.Add

	SheetObj = BookObj.Worksheets(1)

	SheetObj.Range("A1").Value = Label1.Text
	SheetObj.Range("B1").Value = Label2.Text
	SheetObj.Range("C1").Value = Label3.Text
	SheetObj.Range("D1").Value = Label4.Text
	SheetObj.Range("E1").Value = Label5.Text
	SheetObj.Range("F1").Value = Label6.Text

	SheetObj.Range("A2").Value = txtBox1.Text
	SheetObj.Range("B2").Value = txtBox2.Text
	SheetObj.Range("C2").Value = txtBox3.Text
	SheetObj.Range("D2").Value = txtBox4.Text
	SheetObj.Range("E2").Value = txtBox5.Text
	SheetObj.Range("F2").Value = txtBox6.Text

	BookObj.SaveAs("C:\Test.xls")
	ExcelObj.Quit()

End Sub

End Class

 

Should work as a basic "VB"-> Excel. (Not got it installed here to test).

 

General logic is pretty simple, copies labels to A1 B1 etc, and text boxs to A2 B2 etc.

 

Or as asked above, are you referring to VBA not VB?

 

Steve

[Domirankine]

MS Access has a number of Wizard functions that create the SQL statements necessary to import MS Excel workbooks or spreadsheets into Access. These wizards do the CREATE_TABLE steps, including setting keys and data types during table creation, plus can do some data validation during the data population phase. This is probably the easiest way to get your SQL, which you can either then run directly in MySQL, or use to create a copy database in Access which you later import into MySQL database.

[Steve21]

MS Access has a number of Wizard functions that create the SQL statements necessary to import MS Excel workbooks or spreadsheets into Access.

 

But he doesn't have "any" of the data in the excel sheet? It's VB-> Excel. At least from his original post.

 

Steve

[mac_shinobi]

But he doesn't have "any" of the data in the excel sheet? It's VB-> Excel. At least from his original post.

 

Steve

 

As per Losojos, there was no post back to clarify if it was VB or VBA - different altogether.

[EaglesNerd]

Yer this is what i want to do. Do you know how to do this ?

 

Are we talking about Visual Basic (VB) or Visual Basic for Applications (VBA)?

 

If you've developed the form in VBA in Excel and want to add the data entered to the relevant spreadsheet within the workbook containing your VBA project, that shouldn't be too difficult, let us know.

[LosOjos]

First of all, how much experience do you have programming in VBA? And how much experience specifically with VBA for Excel? Because how much explanation is needed is going to depend massively on what you already know...

[EaglesNerd]

Hi LosOjos

 

I am very novice in both VBA and VBA for excel

First of all, how much experience do you have programming in VBA? And how much experience specifically with VBA for Excel? Because how much explanation is needed is going to depend massively on what you already know...

[LosOjos]

Hi LosOjos

 

I am very novice in both VBA and VBA for excel

 

Do you have any experience in VB or programming in general?

[EaglesNerd]

Do you have any experience in VB or programming in general?

 

I have a little VB experience but very minimal consider me as a newbie to programming

[mac_shinobi]

Assuming this is a form in VBA ( not normal VB as per the example above as posted by Steve21 )

 

Unless Losojos beats me to it I can put together an excel sheet with a form / labels / text fields / buttons that will move between rows ( up and down each column for each control ) so

if you had a worksheet that had

 

A1 Forename B1 Surname C1 Age D1 Date of birth

 

John Smith 24 24/8/yyyy

etc

etc

 

Then the first text control would scroll up and down in the first column where names are for John, etc, etc and the 2nd one for Smith etc and so on

 

Can upload this but basically the source code is within each of the buttons for next and previous which are similar, the only difference is previous uses code to take 1 away from the value of source control property and next adds one onto it so if source control for the text field is set to A1, the text field can go up and down in the A column depending on if you click on next or previous

 

Both buttons will have a repeated chunk of code for each control, so the sudo code / explanation of what you would do is :

 

Source Control = "A1"
Split source control so it has a letter and a value
if this is code for next button then use the value from the split command and add one and if for previous button would take one away from the value ie equals split value plus or minus one aka 1
take the above value and concatenate this to the letter you got from the 2nd command so A1 would now become A2 ( A & 2 )
Set the new source control value as the above value ( A2 )

The above code chunk would be repeated for each control in both the next and previous buttons for each text control on the said form you have

will test it but you may have to send a refresh command which will update the source control and also which values are displayed on the form

 

At least thats how I Managed it, can upload an example later on

 

You set the source control on each controls property manually and then the code can adjust each one in the code.

 

Select each text control and in the property window should be a source control or something named similar which links each text control to the spreadsheet cell that is specified at the time so obviously your code would keep changing this depending on which row you are on.

 

I never got around to swapping columns but never saw the need to do that so my form literally navigated up and down for numerous columns at the same time for the multiple controls I had on the form as per above.

Edited March 24, 2011 by mac_shinobi

[mac_shinobi]

Did u need / want an example uploading??