+ Post New Thread
Results 1 to 14 of 14
Office Software Thread, How to export data that you insert in a VB Form into a spreadsheet in Technical; Hi I have created a form in VB and want people to be able to input information into the form ...
  1. #1

    Join Date
    Sep 2010
    Location
    United Kingdom
    Posts
    22
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    How to export data that you insert in a VB Form into a spreadsheet

    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

  2. #2

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,824
    Thank Post
    372
    Thanked 544 Times in 507 Posts
    Rep Power
    184
    Any chance you could post more info on what you want copied? :P or just want a basic outline?

    Steve

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    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.

  4. #4

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,824
    Thank Post
    372
    Thanked 544 Times in 507 Posts
    Rep Power
    184
    Code:
    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

  5. #5

    Join Date
    Mar 2011
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  6. #6

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,824
    Thank Post
    372
    Thanked 544 Times in 507 Posts
    Rep Power
    184
    Quote Originally Posted by Domirankine View Post
    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? :P It's VB-> Excel. At least from his original post.

    Steve

  7. #7

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    10,053
    Thank Post
    3,585
    Thanked 1,123 Times in 1,025 Posts
    Rep Power
    377
    Quote Originally Posted by Steve21 View Post
    But he doesn't have "any" of the data in the excel sheet? :P 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.

  8. #8

    Join Date
    Sep 2010
    Location
    United Kingdom
    Posts
    22
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Yer this is what i want to do. Do you know how to do this ?

    Quote Originally Posted by LosOjos View Post
    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.

  9. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    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...

  10. #10

    Join Date
    Sep 2010
    Location
    United Kingdom
    Posts
    22
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi LosOjos

    I am very novice in both VBA and VBA for excel
    Quote Originally Posted by LosOjos View Post
    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...

  11. #11

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by EaglesNerd View Post
    Hi LosOjos

    I am very novice in both VBA and VBA for excel
    Do you have any experience in VB or programming in general?

  12. #12

    Join Date
    Sep 2010
    Location
    United Kingdom
    Posts
    22
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by LosOjos View Post
    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

  13. #13

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    10,053
    Thank Post
    3,585
    Thanked 1,123 Times in 1,025 Posts
    Rep Power
    377
    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 :

    Code:
    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.
    Last edited by mac_shinobi; 24th March 2011 at 07:45 AM.

  14. #14

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    10,053
    Thank Post
    3,585
    Thanked 1,123 Times in 1,025 Posts
    Rep Power
    377
    Did u need / want an example uploading??



SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2007] Excel Does Not Display Data In Spreadsheet
    By DaveP in forum Office Software
    Replies: 2
    Last Post: 30th November 2012, 05:24 PM
  2. Importing live SIMS data as XML to Google spreadsheet
    By CyberNerd in forum Office Software
    Replies: 4
    Last Post: 4th March 2011, 10:39 AM
  3. [SIMS] Force data export to VLE (Fronter)?
    By localzuk in forum MIS Systems
    Replies: 2
    Last Post: 22nd September 2010, 12:49 PM
  4. Online Form, reults to Databse or Spreadsheet
    By TechSupp in forum Web Development
    Replies: 7
    Last Post: 12th October 2009, 10:29 PM
  5. Netware - User data export
    By pantscat in forum Netware
    Replies: 4
    Last Post: 10th November 2008, 12:58 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •