exporting info from a text box to excel 1

[JesseNorris]

hi all,

i have a text box called totincome which i would like the value of to be exported into my excel file moneyplan.xls to help to automate this spreed sheet.

the reason i would like to do this is my client tells me that people in her line of work(financial counselling) never usualy get the chance to give their clients one of these print outs because if done separetly takes a subsequient interview which most clients never attend.
this aparently is a tool that all the clients should have to help them to get back on their feet.

anyway can someone help me out to make this happen.

p.s
i dont think i have told you that i am only a volunteer and this is the first DB i have developed so any help and guidence is much appreciated.

Jesse
thanks in advance.

 

[Terpsfan]

Being that this is your first database I hope I can make this simple for you. First you want to go into the code window of your form. Then on the menu select Tools, References. Then from the Available References list, check Microsoft Excel Object Library. Then you would type this code at whatever event you want to export to Excel. You have to substitute the spreadsheet name and cell range in your spreadsheet to make this work. txtName is the name of a text box, you would modify that for your purposes as well.

Dim xl as Excel.Application
Set xl = new Excel.Application

xl.Application.Workbooks.Open "C:\moneyplan.xls"
xl.Range("D1") = txtName
xl.Application.ActiveWorkbook.Save
xl.Application.ActiveWorkBook.Close
Set xl = Nothing

 

[JesseNorris]

Thanks heaps
i will give it a try now
Cheers
Jesse

 

[JesseNorris]

Hi Omega36,

i have tyred it but cant see the changes on the spreedsheet

here is the code that i entered

Private Sub totincome_AfterUpdate()
Dim xl As Excel.Application
Set xl = New Excel.Application

xl.Application.Workbooks.Open "C:\database\MoneyPlan.xls"
xl.Range("b5") = totincome
xl.Application.ActiveWorkbook.save
xl.Application.ActiveWorkbook.Close
Set xl = Nothing

End Sub

can you tell me if you can see anything wrong with this code.

also would it be possible to use a templet here insted of a spreedsheet so that the user could change the file name of the spreedsheet when it is updated.

Regards,
Jesse

 

[Terpsfan]

Save your original spreadsheet as an Excel template file extension .xlt. Then the user will be prompted for a file name. Then the spreadsheet will be saved with that file name. I made a slight adjustment to the code. I made the spreadsheet visible.

Dim xl As Excel.Application
    Dim strResponse As String
    Set xl = New Excel.Application
    
    xl.Application.Workbooks.Open "C:\moneyplan.xlt"
    xl.Visible = True
    xl.Range("B5") = totincome
    
    strResponse = InputBox$("What do you want to name this file?", "File Save")
    xl.Application.SaveWorkspace (strResponse)
    
    Set xl = Nothing

 

[JesseNorris]

thanks Omega36
you have been most helpful

can you tell me how to put a password on a command button so that at the onclick event they will be prompted for a password.

once again thanks for your help so far.
Jesse

 

[Terpsfan]

Jesse,

To put a password on a command button you could do something like this. When clicking the command button, the user will be prompted to enter a password via an input box. If the password matches, the rest of the code runs. Otherwise the user is notified that their password was incorrect.

Private Sub CommandButton1_Click()
           Dim strResponse as String
           strResponse = InputBox$("Please enter password")
           
           If strResponse = "Open sesame" Then
                 'Run the rest of your code
           Else
                'Tell the user they entered wrong password
                 MsgBox "Sorry.  Wrong password, try again"
           End If
End Sub

 

[JesseNorris]

thankyou
jesse