V.Urgent: Visual Basic Code Required For Project

[TheBob]

I do Graphics and stuff in VB, so this is just an idea. You could sub-class your speadsheet program from your gui. Once you get this done you can adjust the values of the cells in your speadsheet.

[Bedford]

I am not familiar with all the code in VBE. How would this be done?

[TheBob]

Not shure. I know it can be done, using API calls to windoz .dlls and stuff. Look around.

[Mac_Bug]

Now why would you want to do such an evil thing in excel!

In anycase, the only thing I have experience with is Pascal/Delphi, so I''m just going to take a stab in the dark with pseudo code here.

Is it possible for example, upon pressing the command button, for the program to 'get' the values from textboxes and store it in several variables. And in the excel worksheet cells, make it ='variable' etc. Of course if it works like Delphi, you could for example make it =textbox1.value and it will be already done. Or of course the command button could initiate a procedure that physically puts these values into specific cells, I'm pretty sure the macros in excel would let you do basic manipulations such as that.

As for how? Don't ask me.

[Rodimus]

Public Sub FlexGrid_To_Excel(TheFlexgrid As MSFlexGrid, _
TheRows As Integer, TheCols As Integer, _
Optional GridStyle As Integer = 1, Optional WorkSheetName _
As String)

Dim objXL As New Excel.Application
Dim wbXL As New Excel.Workbook
Dim wsXL As New Excel.Worksheet
Dim intRow As Integer ' counter
Dim intCol As Integer ' counter

If Not IsObject(objXL) Then
MsgBox "You need Microsoft Excel to use this function", _
vbExclamation, "Print to Excel"
Exit Sub
End If

'On Error Resume Next is necessary because
'someone may pass more rows
'or columns than the flexgrid has

'you can instead check for this,
'or rewrite the function so that
'it exports all non-fixed cells
'to Excel

On Error Resume Next

' open Excel
objXL.Visible = True
Set wbXL = objXL.Workbooks.Add
Set wsXL = objXL.ActiveSheet

' name the worksheet
With wsXL
If Not WorkSheetName = "" Then
.Name = WorkSheetName
End If
End With

' fill worksheet
For intRow = 1 To TheRows
For intCol = 1 To TheCols
With TheFlexgrid
wsXL.Cells(intRow, intCol).Value = _
.TextMatrix(intRow - 1, intCol - 1) & " "
End With
Next
Next

' format the look
For intCol = 1 To TheCols
wsXL.Columns(intCol).AutoFit
'wsXL.Columns(intCol).AutoFormat (1)
wsXL.Range("a1", Right(wsXL.Columns(TheCols).AddressLocal, _
1) & TheRows).AutoFormat GridStyle
Next

End

Play around with attributes that the sub is passed and with the control structures on the bottom



I could have found a better example but i am lazy
All you need is right here!
http://www.freevbcode.com/

[Bedford]

It's okay everyone. I got the right code I needed from a guy at the TTLG Forums called David. Quick copy and paste, no fuss. There's a copy below. My problem now is to stop the command button from opening up a new workbook and just stick the data into the current workbook, targetting a specific spreadsheet.

Thanks for your help and advice.



Private Sub CommandButton1_Click()
Dim vbExcel As Excel.Application
Dim vbWB As Excel.Workbook

'creates Excel Obj
Set vbExcel = CreateObject("Excel.Application")
With vbExcel
'makes excel invisible but still leaves it open
.Visible = False
Set vbWB = .Workbooks.Add ' adds a new workbook
With vbWB
'puts the text from the first box into cell A1 of Excel
.ActiveSheet.Range("A21") = TextBox1.Text
'puts the text from the second box into cell A2 of Excel
.ActiveSheet.Range("B21") = TextBox2.Text
End With
MsgBox ("Transfer to Excel Complete")
TextBox1.Text = ""
TextBox2.Text = ""
'makes excel visible
.Visible = True
End With

Set vbWB = Nothing
Set vbExcel = Nothing
End Sub