[stealth_shadow9]

Hey, I'm pretty new to programming. I enjoy it as a hobby, but I am trying to make an inventory .exe. I have figured out how to read an excel file and have it display in my program but am getting hung up on trying to write to cells. Can anyone help? I was trying to use Print#1 as a way to write to a cell but I haven't been able to print to a specific cell like the way I want, for example if I wanted to write my textbox1 to cell B16. I colored the part I am stuck at in red.

Private Sub Form_Load()
 Dim obook As Workbook
   Dim osheet As Worksheet
   Dim var As Variant
   Dim var2 As Variant
   Dim var5 As Variant
If Dir("C:\Documents and Settings\All Users\Desktop\Test.xls") <> "" Then 'check for this file

ElseIf Dir("C:\Documents and Settings\All Users\Desktop\Test.xls") = "" Then
Open "C:\Documents and Settings\All Users\Desktop\Test.xls" For Append As #1

Print #1, "Part Number" & vbTab & "Description" & vbTab & "Quantity" 'put these columns in fil

Text3.Text = "1"


   


 Set obook = OLE1.object
   Set osheet = obook.Sheets(1)
Do Until Text3.Text = "800"
var = osheet.Range("B" + Text3.Text).Value
var2 = osheet.Range("A" + Text3.Text).Value
var5 = osheet.Range("C" + Text3.Text).Value
Text2.Text = var
Text5.Text = var5
Text3.Text = Text3.Text + 1
Print #1, var2 & vbTab & var & vbTab & var5

Loop
MsgBox "File was created On desktop"
 obook.Close

   


End If



Combo1.Text = ""
Text3.Text = 1
Text2.Text = " "

End Sub


Private Sub Combo1_KeyPress(KeyAscii As Integer) 'press enter in part number starts search
 If KeyAscii = 13 Then ' The ENTER key.
 
            Command1 = 1
         End If
      End Sub


[color="#FF0000"]Private Sub Text5_KeyPress(KeyAscii As Integer) 'press enter in Quantity writes amount to part number
 If KeyAscii = 13 Then ' The ENTER key.

   Dim xlApp As Object
   Dim wb As Object
   Dim ws As Object
   Dim var9 As Variant
   Dim var10 As Variant
Set xlApp = New Excel.Application

   Set wb = xlApp.Workbooks.Open("C:\Documents and Settings\All Users\Desktop\Test.xls")

   Set ws = wb.Worksheets("Test").Range(Text1.Text)

   

  var9 = ws.Range("C9") = "Text5.Text"
  
  
   
  
wb.Close
   xlApp.Quit

   Set ws = Nothing
   Set wb = Nothing
   Set xlApp = Nothing
   MsgBox "this is working"
   Combo1.SetFocus
   
 End If
[/color]
        
         
      End Sub


Private Sub Command1_Click() 'searches for item
  Dim xlApp As Excel.Application
   Dim wb As Workbook
   Dim ws As Worksheet
   Dim var As Variant
   
Text2.Text = " "
   Text3.Text = 1
   Text4.Text = ""
   Label2.Visible = True
   Command1.Enabled = False

   Text5.SetFocus
 
If Option1 = True Then 'searches by description

Set xlApp = New Excel.Application

   Set wb = xlApp.Workbooks.Open("C:\Documents and Settings\All Users\Desktop\Test.xls")

   Set ws = wb.Worksheets("Test") 'Specify your worksheet name

   
Do Until Combo2.Text = Text2.Text Or Text2.Text = "" Or Text3.Text = "800"

var = ws.Range("B" + Text3.Text).Value
var2 = ws.Range("A" + Text3.Text).Value
var5 = ws.Range("C" + Text3.Text).Value
Text2.Text = var
Text5.Text = var5
Text4.Text = var2
Text3.Text = Text3.Text + 1

Loop
  wb.Close
xlApp.Quit
Set ws = Nothing
   Set wb = Nothing
   Set xlApp = Nothing

'------------------------------------------------------------------------------------------------

If Combo2.Text = Text2.Text Then


Text4.Text = var2

Label2.Visible = False
Command1.Enabled = True
Text3.Text = 1
Text2.Text = " "
 

   Set ws = Nothing
   Set wb = Nothing
   Set xlApp = Nothing
End If

ElseIf Option2.Value = True Then

   Set xlApp = New Excel.Application
  Set wb = xlApp.Workbooks.Open("C:\Documents and Settings\All Users\Desktop\Test.xls")

   Set ws = wb.Worksheets("Test") 'Specify your worksheet name

Do Until Combo1.Text = Text2.Text Or Text2.Text = "" Or Text3.Text = "800"
var = ws.Range("A" + Text3.Text).Value
var2 = ws.Range("B" + Text3.Text).Value
var5 = ws.Range("C" + Text3.Text).Value
Text2.Text = var
Text4.Text = var2
Text5.Text = var5
Text3.Text = Text3.Text + 1

 Text1.Text = ("C" & (Text3.Text - 1))


 Loop

    wb.Close
xlApp.Quit
Set ws = Nothing
   Set wb = Nothing
   Set xlApp = Nothing
  
If Combo1.Text = Text2.Text Then

Text4.Text = var2

Label2.Visible = False
Command1.Enabled = True

Text2.Text = " "


End If


   Set ws = Nothing
   Set wb = Nothing
   Set xlApp = Nothing
End If


End Sub




Private Sub Option1_Click()
Combo1.Enabled = False
Combo2.Enabled = True

End Sub

Private Sub Option2_Click()
If Option2 = True Then
Combo1.Enabled = True
Combo2.Enabled = False

End If

End Sub

Sub SelectAllText(Textbox5 As TextBox)

Textbox5.SelStart = 0
Textbox5.SelLength = Len(Textbox5.Text)

End Sub

Private Sub Text5_GotFocus()

SelectAllText Text5

End Sub

For whatever reason the color doesnt seem to work, so the problem I am having starts at line 057 and ends at 087

[BobRodes]

Your problem is on line 73. In VB, you are actually saying that var9 equals true if your range equals the string "Text5.Text", and false if it doesn't. The VB = operator is overloaded to perform both assignment and equality checking. So, the statement
x = a = b
is equivalent to (and a useful shorthand for)
If a = b then x = true else x = false
or
x = Iif(a = b, true, false)
called "inline if" in VB.

All you need to do is this:
ws.Range("C9") = Text5.Text

Also, you should put "Option Explicit" at the top of your code, so any variables you use have to be declared. Otherwise, you will turn what should be syntax errors into logical errors which are much harder to find. Witness a case in point here; if you had it at the top you would get an error that var9 was undeclared. That might have started you in the right direction. It might not of course, but any information is helpful.

This post has been edited by BobRodes: 15 June 2012 - 06:36 AM

[stealth_shadow9]

BobRodes, on 15 June 2012 - 06:34 AM, said:

Your problem is on line 73. In VB, you are actually saying that var9 equals true if your range equals the string "Text5.Text", and false if it doesn't. The VB = operator is overloaded to perform both assignment and equality checking. So, the statement
x = a = b
is equivalent to (and a useful shorthand for)
If a = b then x = true else x = false
or
x = Iif(a = b, true, false)
called "inline if" in VB.

All you need to do is this:
ws.Range("C9") = Text5.Text

Also, you should put "Option Explicit" at the top of your code, so any variables you use have to be declared. Otherwise, you will turn what should be syntax errors into logical errors which are much harder to find. Witness a case in point here; if you had it at the top you would get an error that var9 was undeclared. That might have started you in the right direction. It might not of course, but any information is helpful.

The Actual cell I want to write to will change based off of what I am searching for. So C9 will not be constant. I would like to use a textbox to represent the cell I want to write to. so essentially i.e. ws.range(textbox2.text) = text5.text is what I am looking for.

[BobRodes]

That sounds fine. However, you might want also to trap the error that could result from putting in an invalid range name or coordinate. If you'd like to explore that, let me know and I'll get a bit more information from you.

[stealth_shadow9]

BobRodes, on 15 June 2012 - 08:00 AM, said:

That sounds fine. However, you might want also to trap the error that could result from putting in an invalid range name or coordinate. If you'd like to explore that, let me know and I'll get a bit more information from you.

I'm not saying that it will work. But essentially I want the program to reference to the textbox with the cell number in it then I want another text box to be written into that cell on the spreadsheet. does that make sense? I'm not sure if I'm explaining this well.

[BobRodes]

Sure. So, the range element (which can of course be just one cell) is expressed as a string, e. g. .Range("SomeRange"). And the contents of the cell is a string too. And the Text property of any TextBox is a string. So, you can quite happily do this:

ws.Range(txtBoxWithARangeLocationInIt.Text) = txtBoxWithACellValueInIt.Text

By the way, this is called "Self-Documenting Code." It's all the rage right now. Pretty, isn't it?

Again, this will work fine if the user cooperates. If the user puts in an invalid range name in the text box, he'll get an error. Which you may want to trap, if the error you get by default looks unfriendly.

This post has been edited by BobRodes: 15 June 2012 - 12:48 PM

[stealth_shadow9]

BobRodes, on 15 June 2012 - 12:45 PM, said:

Sure. So, the range element (which can of course be just one cell) is expressed as a string, e. g. .Range("SomeRange"). And the contents of the cell is a string too. And the Text property of any TextBox is a string. So, you can quite happily do this:

ws.Range(txtBoxWithARangeLocationInIt.Text) = txtBoxWithACellValueInIt.Text

By the way, this is called "Self-Documenting Code." It's all the rage right now. Pretty, isn't it?

Again, this will work fine if the user cooperates. If the user puts in an invalid range name in the text box, he'll get an error. Which you may want to trap, if the error you get by default looks unfriendly.

Hey this worked great! The only weird part is that it writes to a different column than what I want. i.e I want to write to c11 and it writes to e11. It always writes 2 columns over. I adjusted my code on line 128 to say "A" instead of "C" which now places my value in the desired place. But It's just odd to me that it would do that. Otherwise it works Any ideas on the 2 column issue?
Thanks again for the help.

[BobRodes]

Strange. Can you show your code? Any code that references the text box that has the value in it could be relevant.

This post has been edited by BobRodes: 16 June 2012 - 10:31 AM