Saving form submitted by user to Excel File

[skilpad]

Hi all,

I'm new to Visual Basic/Studio 6. I have a small application form where I have to submit some information. All this is working fine and I'm able to write these information to an excel file.

I need now to write eg name to row 7 column A, surname to row 8 column B, etc. Currently it is writing the info submitted in the top left corner.

Any help will be appreciated.

Thanks

[albertw]

Hi all,

I'm new to Visual Basic/Studio 6. I have a small application form where I have to submit some information. All this is working fine and I'm able to write these information to an excel file.

I need now to write eg name to row 7 column A, surname to row 8 column B, etc. Currently it is writing the info submitted in the top left corner.

Any help will be appreciated.

Thanks

hi
try this
you may need to alter filenames
note that the code in not tested !
leave it to your imagination :)

Private appExel As Excel.Application
Private wbBook As Excel.Workbook
 
Private Sub Form_Load()
    Dim rngExelRange As Excel.Range
    On Error Resume Next                                
    Set appExel = GetObject(, "Excel.Application")
    ' find out if Exel is running already
    If Err.Number <> 0 Then  Set appExel = CreateObject("Excel.Application")
    Err.Clear    
    On Error GoTo Fail
    ' this spreadsheet is called Sheet.xls
    Set wbBook = appExel.Workbooks.Open(App.Path & "\Sheet.xls")
    FreeNum = FreeFile
    ' the datafile containing item to put into Sheets.xls is called Filename
    Open Filename for Input as #FreeNum
    Line Input #FreeNum, p
    ' data in the file 'filename' is supposed to have a fixed sequence
    ' from which you can take the induvidual data an put this in your
    ' Exel worksheet
    rngExelRange.Cells(7,1) = p         ' range("A7")
    ' get next item 
    Line Input #FreeNum, p
    ' and put this in the worksheet
    rngExelRange.Cells(8,2) = p         'range("B8")
Fail:
    Err.Clear
    Close 
    ' make things tidy
    Set appExel = Nothing
    Set wbBook = Nothing
End Sub
 

[albertw]

hi
try this
you may need to alter filenames
note that the code in not tested !
leave it to your imagination :)

Private appExel As Excel.Application
Private wbBook As Excel.Workbook
 
Private Sub Form_Load()
    Dim rngExelRange As Excel.Range
    On Error Resume Next                                
    Set appExel = GetObject(, "Excel.Application")
    ' find out if Exel is running already
    If Err.Number <> 0 Then  Set appExel = CreateObject("Excel.Application")
    Err.Clear    
    On Error GoTo Fail
    ' this spreadsheet is called Sheet.xls
    Set wbBook = appExel.Workbooks.Open(App.Path & "\Sheet.xls")
    FreeNum = FreeFile
    ' the datafile containing item to put into Sheets.xls is called Filename
    Open Filename for Input as #FreeNum
    Line Input #FreeNum, p
    ' data in the file 'filename' is supposed to have a fixed sequence
    ' from which you can take the induvidual data an put this in your
    ' Exel worksheet
    rngExelRange.Cells(7,1) = p         ' range("A7")
    ' get next item 
    Line Input #FreeNum, p
    ' and put this in the worksheet
    rngExelRange.Cells(8,2) = p         'range("B8")
Fail:
    Err.Clear
    Close 
    ' make things tidy
    Set appExel = Nothing
    Set wbBook = Nothing
End Sub
 

hi again
second best option is to create a .csv (comma separated values) file
disadvantage of such a file is that data must be entered in a sequence.

Open App.Path & "\Exel.csv" for output as #1
Print #1,Value1;",";Value2;",";Value3
Close #1

the amount of commas between values will determine the column
every new Print #1 statement determines the row.

[skilpad]

Thanks for the reply - please ignore first question about excel - is actually word document.

I have a form (see below) with 2 text fields and a submit button. I can save data to the file but in sequence below each other. What I want to do is: to open an existing word document (a form that I usually print out and fill in information required manually with a pen). The existing contents of the file is something like:

Hello, my name is _______ and my surname is _______!!

The existing word file may contain blocks, tables, lines, etc.

Instead to write it with a pen, the vb application must open up this file and enter the name and surname fields from the form.

Almost like PHP where I use variables eg.

Hello, my name is $name and my surname is $surname!!

And will it be possible and easy to save the file as the name entered by user instead of using a fixed filename?


[PHP]

Private Sub Command1_Click()
Dim hFile As Long
Dim sFilename As String

sFilename = "f:\mac.xls"

'obtain the next free file handle from the
'system and and save the text box contents
hFile = FreeFile
Open sFilename For Output As #hFile
Print #hFile, ("Name: "), Text1.Text
Print #hFile, ("Surname: "), Text2.Text

Close #hFile

Unload Me

MsgBox "Thanks for submitting your MAC address"
End Sub

[/PHP]

[albertw]

Thanks for the reply - please ignore first question about excel - is actually word document.

I have a form (see below) with 2 text fields and a submit button. I can save data to the file but in sequence below each other. What I want to do is: to open an existing word document (a form that I usually print out and fill in information required manually with a pen). The existing contents of the file is something like:

Hello, my name is _______ and my surname is _______!!

The existing word file may contain blocks, tables, lines, etc.

Instead to write it with a pen, the vb application must open up this file and enter the name and surname fields from the form.

Almost like PHP where I use variables eg.

Hello, my name is $name and my surname is $surname!!

And will it be possible and easy to save the file as the name entered by user instead of using a fixed filename?


[PHP]

Private Sub Command1_Click()
Dim hFile As Long
Dim sFilename As String

sFilename = "f:\mac.xls"

'obtain the next free file handle from the
'system and and save the text box contents
hFile = FreeFile
Open sFilename For Output As #hFile
Print #hFile, ("Name: "), Text1.Text
Print #hFile, ("Surname: "), Text2.Text

Close #hFile

Unload Me

MsgBox "Thanks for submitting your MAC address"
End Sub

[/PHP]

sry, no experience in programming where word is involved.