Using a Command Button to export the value of a textbox to a specific cell in excel

[mdschuetz]

Is this possible and if so, can someone please post me a walkthough?

 

[Premy]

How about:

Sub btnXL_Click()
Dim objXL as Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet

Set objXL as New Excel.Application
Set objWB = objXL.Workbooks.Open("C:\MyXl.xls")
Set objWS = objWB.Worksheets("Sheet2")

With objWS
.Cells(1, 1).Value = "Hello"
.Cells(2, 1).Value = Me.txtXl
End With

objXL.Visible = True

End Sub

HTH
Premy

 

[Davros]

hi
the code above works with access 2003 if i change the line:

Set objXL as New Excel.Application
to
Set objXL = Excel.Application

however it overwrites the same cell in the excell worksheet:
.Cells(2, 1).Value = Me.txtXl

how would you make sure that the text you export from access goes into a different cell in excel each time? something along the lines of a formula A1 + 1, A2 + 1, A3 + 1 each time you export?

thanks

 

[mdschuetz]

I can't get it to write anything.

Private Sub btnXL_Click()
Dim objXL As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet

Set objXL = Excel.Application
Set objWB = objXL.Workbooks.Open("L:\New L Drive\05, Supply Folders\02, PUR-1E\Requisitions\2007\PUR-1E.xls")
Set objWS = objWB.Worksheets("Form")

With objWS
.Cells(1, 1).Value = "Hello"
.Cells(2, 1).Value = Me.txtTotal
End With

objXL.Visible = True

End Sub

No errors, but no effect at all on the worksheet. Nothing transfers.

 

[Premy]

how would you make sure that the text you export from access goes into a different cell in excel each time? something along the lines of a formula A1 + 1, A2 + 1, A3 + 1 each time you export?

thanks

Well yeah, you'll have to put up some routine to return the value from, let's say cell 1 to cell 1000 on that column, check which is the first empty cell downwards, and use that cell's coordinates in cell filling code.

HTH
premy

 

[Premy]

I can't get it to write anything.
No errors, but no effect at all on the worksheet. Nothing transfers.

Well, does the workbook get's opened at least? U may wanna do a test on a shorter path like "C:\test.xls", just to see if this could be an issue with the path.

 

[Premy]

Oh and u may wanna test some different settings on your macro sec levels.

 

[KeithG]

You need the New Keyword to open an instance of excel

Dim objXL As New Excel.Application


Or with late binding it would be

Dim objXL as object

set objXL=CreateObject("Excel.Application")

 

[Premy]

That's right Keith, thanks, I missed that one in mdschuetz's reply.

Regards,
Premy