Reg : Excel

I am Happy to meet you all :)
I am learning Visual Basic now.
Thru Visual Basic 6.0, Can we create an Excel file using a Template file which is in my local Drive and passing some values to that file, and finally saving it as a different name (Which is not my Template file's Name) at different location.
Here is the code, which I got from one site. ( slightly modified )

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oBook.open = ("C:\BOM") ' (Added Line)
oBook = oExcel.Workbooks.Add

'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "S.No."
oSheet.Range("B1").Value = "Description"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "1"
oSheet.Range("B2").Value = "Nothing"

'Save the Workbook and Quit Excel
oBook.SaveAs("D:\BOMS\200378_BOM.xls")
oExcel.Quit()

Welcome to the forum!
When ever you use an object variable you must use a SET statement to define the reference.

Try changing you code

VB Code:

oExcel = CreateObject("Excel.Application")
oBook.open = ("C:\BOM") ' (Added Line)
oBook = oExcel.Workbooks.Add
 
'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)

to

VB Code:

Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Open("C:\BOM")
    Set oSheet = oBook.Worksheets(1)

This should address your problem.

Also, when posing code, please make sure you add vbcode tags. (it makes it much easier to read)

Welcome to the Forums.

You need to set your obook object first.

For VB 6 ...

VB Code:

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
 
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("C:\BOM.xls") ' (Added Line)
'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 = "S.No."
oSheet.Range("B1").Value = "Description"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "1"
oSheet.Range("B2").Value = "Nothing"
 
'Save the Workbook and Quit Excel
oBook.SaveAs FileName:="D:\BOMS\200378_BOM.xls"
oBook.Saved = True
Set oSheet = Nothing
oBook.Close
Set oBook = Nothing
oExcel.Quit
Set oExcel = Nothing

:p :p Beat you to it...

But I fixed all the code :p :lol:

Ah yes, I missed the errors at the end...

So by the time you fixed your errors I would have posted mine before you. So in essence, I beat you. :D


Its always good to have multiple sources/views of input when solving a problem. ;)

Thank you very very much, Kenny and Rog,
Greatly, it is working. :thumb:
Could you please give me a hint, How to link to Excel cells with my GUI Textbox.
I already prepared my GUI and the great thing is to link the TEXTBOX's with my Excel file's CELL's.
For example ...

oSheet.Range("c12").Value = "202154-A-4587"

Here, the Value for "c12" is a string, instead of a string I would like to link this CELL with my "form1.vb" textbox "PART NAME".

Hope you will do the needful.

Thank you both VB Professionals, agian.

Alen

I think your using VB.NET by chance?

To do what you want is similar in both.

VB Code:

TextBox1.Text = oSheet.Range("c12").Value

Dear Rob,
It is working fine and you made my work to be simple.
I am using Visual Basic only.
Now, I am thinking that can we create a loop here.

Please, find the attached .jpg file, in which I mentioned clearly what exactly I need.
This is just for my Information and there no need o urgency.

Thank you.

Alen

I am apologized for the Incomplete question.
Here once I press Next, the entire data should be passed to the respective excel sheet and the dialog again prompt me to do the same, until unless it is cancelled.
Is this possible to make it as loop ?

Thanks.
Alen

Why not use an Excel UserForm instead of VB.NET ?

do you have something else tied in to this? So you want the form to view each sheets data for read/write?

Use the oBook.Sheets("Sheet2").Select method for switching the view of the sheet. Just reference which sheet you are going to be reading/writting to/from.

Dear Rob,
I am trying to give a LOOP condition, so that once all the values are passed to excel Sheet's FIRST ROW, then agian it should prompt the user to enter the same values for the SECOND ROW.
This should happen until unless I CANCEL my GUI, Because I am having so many parts to enter them with their details as shown in the previous attachment.

Hope you understood my intension.
Thanking you,
Alan

Dear Rob,
I might not able to frame my query in a proper way, I suppose.
really, I tried so many times, I searched some sites and finally I failed at this point. Please find the attachment named "Query.jpg".

Thank you for your time.

Alen.

Dear Rob,
I stucked here and I am unable to move further.
Please, Help me out.
TIA

Alen

Public Class Form3
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Open("X:\BOM.xls") ' (Added Line)
'oBook = oExcel.Workbooks.Add

'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)
oSheet.Range("d3").Value = Form2.TextBox1.Text
oSheet.Range("d4").Value = Form2.TextBox2.Text
oSheet.Range("d5").Value = Form2.TextBox3.Text
oSheet.Range("N2").Value = Form2.TextBox4.Text
oSheet.Range("o6").Value = Form2.TextBox5.Text

If Me.RadioButton1.Checked = True Then
oSheet.Range("k10").Value = Form2.TextBox3.Text
oSheet.Range("l10").Value = Form2.TextBox3.Text
End If

If Me.RadioButton2.Checked = True Then
oSheet.Range("k10").Value = Form2.TextBox3.Text
oSheet.Range("l10").Value = "00"
End If

oSheet.Range("d10").Value = Me.TextBox1.Text 'Part No.
oSheet.Range("e10").Value = Me.TextBox2.Text ' Description
oSheet.Range("f10").Value = Me.TextBox3.Text ' Assy No

'some times I may get only "5 Parts" and sometimes it might be around 200.
'hence each time I am not able to declare the Range for each textbox.
' Here I need to get REPEATEDLY the current form when I click "NEXT"
' to enter varied values of "Part No.", "Description", "Assy No" for ROW-2 and again for ROW-3 and again for ROW-4 ... 5 ... 6..
' and when I click "OK" the data should pass into my worksheet.


'' XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

'Save the Workbook and Quit Excel
oBook.SaveAs(FileName:="C:\BOM.xls")
oBook.Saved = True
oSheet = Nothing
oBook.Close()
oBook = Nothing
oExcel.Quit()
oExcel = Nothing


Me.Hide()

End Sub

i define with SET statement but i found error message "compile error : invalid outside procedure",sorry i'm still newbie and i 'm very happy if you will guide me more :Big Green: