Saving

[Mikshake]

Hi Gurus

Firstly I would like to say... I know nothing about programming or VBA. I'm actually a geologist who has given up playing with rocks to earn some real money! Hahahaha. Anyway...
I have written most of what we need, the problem I am facing is getting it to save the data. I have 7 forms, the first starts with name date etc. I would like to be able to save the worksheet at the end of the first form and have it open it on the second.

I did have it saving the worksheet, however, something went t~ts up and now it won't and I cannot get the code to work.

The top of the form is set as

[VBA]
Private Sub command1_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oExcelSheet As Object
Dim counter As Integer

'Start a new workbook in Excel
Set oExcelSheet = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "name"
oSheet.Range("B1").Value = "name1"
oSheet.Range("A2").Value = "company"
oSheet.Range("B2").Value = "com"
[/VBA]
Then to save we have....

[VBA]
Excelsheet.SaveAs FileName:="d:\testy.xls"
[/VBA]
I then need to be able to open it up in the second form, which i have no idea about.

Could someone help me out? Please!

Cheers for looking anyway!!!

[Bob Phillips]

We can't see the code it context.

Trust me, we know how to save a workbook, so seeing that line doesn't rock our boats much. What we need to see is the code where you initaite the form, the form exit code, and what code is excuted on form exit.

[Mikshake]

Hey

Here is the entire thing.... gulp!

[vba]
Private Sub Course_DropDown()
' Fill the list box
With Course
.AddItem "Petrel Introduction"
.AddItem "Petrel Seismic Visualisation"
.AddItem "Petrel Property Modeling"
.AddItem "Petrel Process Manager"
.AddItem "Petrel Well Correlation"
.AddItem "Petrel Reservoir Engineering"
.AddItem "Petrel Applied Mapping"
.AddItem "Petrel Structural Modeling"
.AddItem "Interactive Petrophysics - Fundamentals"
.AddItem "Interactive Petrophysics - Advanced"
End With

' Select the first list item
Course.ListIndex = 0
End Sub
[/vba]
[vba]
Private Sub trainer1_DropDown()
' Fill the list box
With trainer1
.AddItem "Steve Adams"
.AddItem "Gabriel Angheluta"
.AddItem "Chris Banks"
.AddItem "Emily Campbell"
.AddItem "Emer Caslin"
.AddItem "Berit Davies"
.AddItem "Mark Douglas"
.AddItem "Xavier Dellamonica"
.AddItem "Michaela Farrow"
.AddItem "Graeme Gillies"
.AddItem "Christy Glatz"
.AddItem "Guido Van der Hoff"
.AddItem "Hemant Kumar"
.AddItem "Pippa Murphy"
.AddItem "Reza Nazarian"
.AddItem "Richard Nice"
.AddItem "Lyudmila Ouagueni"
.AddItem "Lorna Parkes"
.AddItem "Euan Robb"
.AddItem "Marine Segnole"
.AddItem "Susannah Trickett"
.AddItem "Leigh Truelove"
.AddItem "Other"
End With

' Select the first list item
trainer1.ListIndex = 0
End Sub
[/vba]
[vba]
Private Sub trainer2_DropDown()
' Fill the list box
With trainer2
.AddItem "Steve Adams"
.AddItem "Gabriel Angheluta"
.AddItem "Chris Banks"
.AddItem "Emily Campbell"
.AddItem "Emer Caslin"
.AddItem "Berit Davies"
.AddItem "Mark Douglas"
.AddItem "Xavier Dellamonica"
.AddItem "Michaela Farrow"
.AddItem "Graeme Gillies"
.AddItem "Christy Glatz"
.AddItem "Guido Van der Hoff"
.AddItem "Hemant Kumar"
.AddItem "Pippa Murphy"
.AddItem "Reza Nazarian"
.AddItem "Richard Nice"
.AddItem "Lyudmila Ouagueni"
.AddItem "Lorna Parkes"
.AddItem "Euan Robb"
.AddItem "Marine Segnole"
.AddItem "Susannah Trickett"
.AddItem "Leigh Truelove"
.AddItem "Other"
End With

' Select the first list item
trainer2.ListIndex = 0
End Sub[/vba]

[vba]Private Sub Form_Load()
todaydate.Text = Date
End Sub[/vba]

[vba]Private Sub command1_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oExcelSheet As Object
Dim counter As Integer
'Start a new workbook in Excel
Set oExcelSheet = CreateObject("Excel.Application")
oSheet.Range("A1").Value = "name"
oSheet.Range("B1").Value = "name1"
oSheet.Range("A2").Value = "company"
oSheet.Range("B2").Value = "com"
oSheet.Range("B3").Value = "Course"
oSheet.Range("B4").Value = "trainer1"
oSheet.Range("B5").Value = "trainer2"
oSheet.Range("B6").Value = "todaydate"

If name1.Enabled = True Then
oSheet.Range("B1").Value = name1.Text
End If
If com.Enabled = True Then
oSheet.Range("B2").Value = com.Text
End If
If Course.Enabled = True Then
oSheet.Range("B3").Value = Course.Text
End If
If trainer1.Enabled = True Then
oSheet.Range("B4").Value = trainer1.Text
End If
If trainer2.Enabled = True Then
oSheet.Range("B5").Value = trainer2.Text
End If
If todaydate.Enabled = True Then
oSheet.Range("B6").Value = todaydate.Text
End If
Form1.Visible = False
Form2.Visible = True

Excelsheet.SaveAs FileName:="d:\testy.xls"

End Sub
[/vba]

[Bob Phillips]

Okay, so you start a new instance of Excel from the form commandbutton (why eludes me, but let's gloss over that for now), and then you try to write to a worksheet, but where do you get a workbook, or setup the oSheet variable?

I think you are confused, maybe it should be

[vba]
Dim oExcel As Object
Dim oBook As Object
Dim oExcelSheet As Object
Dim counter As Integer
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oExcleSheet = oBook.Worksheets(1)
oExcelSheet.Range("A1").Value = "name"
oExcelSheet.Range("B1").Value = "name1"
oExcelSheet.Range("A2").Value = "company"
oExcelSheet.Range("B2").Value = "com"
oExcelSheet.Range("B3").Value = "Course"
oExcelSheet.Range("B4").Value = "trainer1"
oExcelSheet.Range("B5").Value = "trainer2"
oExcelSheet.Range("B6").Value = "todaydate"
[/vba]

[Simon Lloyd]

Also this line:
[VBA]Excelsheet.SaveAs FileName:="d:\testy.xls"
[/VBA]should read:
[VBA]
oExcelSheet.SaveAs FileName:="d:\testy.xls"

[/VBA]

[Bob Phillips]

Also, if I am right, that should be oBook, not even oExcelSheet.

[Simon Lloyd]

Humble apolgies! you are of course right!, i didn't read all that code.....!

[Bob Phillips]

No, no, it is only a thought. I was just adding to your comment.

There might be something else going on that I missed, the OP did say that it used to work, I just can't see how.

[Simon Lloyd]

Well Bob, there are declarations there that don't allude to anything in the code! Maybe that last line should have been:
[VBA]
oExcelsheet.Copy
ActiveworkBook.SaveAs FileName:="d:\testy.xls"

[/VBA]

[Bob Phillips]

Does Activeworkbook work when you are using Excel in automation? I really don't know, I have never used it in that context, I always set an object variable.

But again, I still wonder why he is using automation?

[Mikshake]

Hi...

Thanks for all the replies I managed to get it to save (wooooooooooooo)!!!!

The next problem I have is that i have 7 forms. At the end of the first form I have saved the data from that form. When i come to the next form how do i tell vba to use that same saved excel spreadsheet to record the data? Or can I set some kind of global variables set that will be used by all forms and then just save it at the end of the very last form?

Thank you for help so far and yes I still have no idea what I am doing... yes i am confused!!!

[Aussiebear]

How big is your workbook? Can you post it so we can see what it is that you are trying to to?