View Full Version : V.Urgent: Visual Basic Code Required For Project
29th Mar 02, 8:55 AM
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.
29th Mar 02, 9:13 AM
I am not familiar with all the code in VBE. How would this be done?
29th Mar 02, 3:06 PM
Not shure. I know it can be done, using API calls to windoz .dlls and stuff. Look around.
29th Mar 02, 3:51 PM
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.
29th Mar 02, 4:31 PM
Public Sub FlexGrid_To_Excel(TheFlexgrid As MSFlexGrid, _
TheRows As Integer, TheCols As Integer, _
Optional GridStyle As Integer = 1, Optional WorkSheetName _
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"
'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
On Error Resume Next
' open Excel
objXL.Visible = True
Set wbXL = objXL.Workbooks.Add
Set wsXL = objXL.ActiveSheet
' name the worksheet
If Not WorkSheetName = "" Then
.Name = WorkSheetName
' fill worksheet
For intRow = 1 To TheRows
For intCol = 1 To TheCols
wsXL.Cells(intRow, intCol).Value = _
.TextMatrix(intRow - 1, intCol - 1) & " "
' format the look
For intCol = 1 To TheCols
wsXL.Range("a1", Right(wsXL.Columns(TheCols).AddressLocal, _
1) & TheRows).AutoFormat GridStyle
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!
30th Mar 02, 5:45 AM
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")
'makes excel invisible but still leaves it open
.Visible = False
Set vbWB = .Workbooks.Add ' adds a new workbook
'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
MsgBox ("Transfer to Excel Complete")
TextBox1.Text = ""
TextBox2.Text = ""
'makes excel visible
.Visible = True
Set vbWB = Nothing
Set vbExcel = Nothing
Powered by vBulletin® Version 4.2.0 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.