Excel file not saving correctly

[Alpha03]

I wrote a little VB program (below) to input a row of data into an already existing Excel file based on the information I provide through the FORM of the VB program. The only problem is that it doesn't save properly. When I go to open the file to see if the changes are made, the file isn't even there anymore. (Thank god I tested this program with a copy of the actual file instead, otherwise I would have lost a good year's worth of records). The file will still be there, however, when I open it, the Microsoft Excel Application Program loads, but it just opens to a grey interface (like if you were to manually load Excel, and then close out the already opened workbook). I also played with the 'objExcel.Application.Visible = True' statement and it does the same thing. It loads the Excel Application program, but not the file itself. However, I also tested this same exact statement on a Microsoft Word document and it worked fine...MS Word Application launched AND the file opened as well. This does not work with my Excel files for some reason. Just keep in mind that all I'm trying to accomplish with this program is I want to simply input records through this program and saving them to the Excel file WITHOUT actually opening the Excel file (as in seeing it on computer screen). Someone please help me! Thanks.

**************************************************

Dim objExcel As Object

Private Sub Command1_Click()
  Dim numRecords As Integer
  numRecords = objExcel.Worksheets("Main").Range("B5").Value
  currentRow = numRecords + 7
  objExcel.Worksheets("Main").Range("A" & currentRow).EntireRow.Insert
  currentRow = currentRow + 1
  objExcel.Worksheets("Main").Range("A" & currentRow & ":" & "I" & currentRow).Copy
  currentRow = currentRow - 1
  objExcel.Worksheets("Main").Paste Destination:=objExcel.Worksheets("Main").Range("A" & currentRow)
  currentRow = currentRow + 1
  objExcel.Worksheets("Main").Range("A" & currentRow).Value = Text1.Text
  objExcel.Worksheets("Main").Range("B" & currentRow).Value = Text2.Text
  objExcel.Worksheets("Main").Range("C" & currentRow).Value = Combo3.Text
  objExcel.Worksheets("Main").Range("D" & currentRow).Value = Text4.Text
  objExcel.Worksheets("Main").Range("E" & currentRow).Value = Text5.Text
  objExcel.Worksheets("Main").Range("F" & currentRow).Value = Text6.Text
  objExcel.Worksheets("Main").Range("G" & currentRow).Value = Text7.Text
  objExcel.Worksheets("Main").Range("H" & currentRow).Value = Text8.Text
  objExcel.Worksheets("Main").Range("I" & currentRow).Value = Text9.Text
End Sub

Private Sub Command2_Click()
  objExcel.Save
  Set objExcel = Nothing
  End
End Sub

Private Sub Form_Load()
  Set objExcel = GetObject("[Here is location of Excel file]")
End Sub

**************************************************

 

[VBAjedi]

If I'm reading your code right, once someone clicks Command2 you're gonna lose your reference to objExcel and your code is dead in the water for any subsequent attempts. I think I would be inclined to set objExcel at the beginning of each procedure that uses it (rather than just once when the form loads). . .

VBAjedi

 

[johnwm]

You need to open the Workbook specifically:

    Set objExcel = CreateObject("Excel.Application") 
    objExcel.Workbooks.Open ("c:\j3.xls") 
    objExcel.Visible = True

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

For tsunami relief donations

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts