|
Subject:
|
Excel automating problem
|
|
Posted By:
|
xwang4
|
Post Date:
|
9/18/2003 3:54:19 PM
|
Hello
I'm doing a project which uses the excel object. I have tried out that i can create a new excel application, get the sheet and make some change, then save it to my harddrive. I can find the new file with all the changes I made. My problem is that I have to open an exiting .xls file(because the format is required), and fill out it with new information, then save it as a new file. There is my code
Dim oExcel, oBook, oSheet As Object
Const sSampleFolder = "F:\ExcelFile\"
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Open(sSampleFolder & "accounting.xls")
oSheet = oBook.Worksheets(1)
oSheet.Range("D9").Value = "The Graduate School"
oSheet.Range("D10").Value = "Lawson Accounting Unit"
oSheet.Range("D11").Value = "Setup & Upload Spreadsheet"
Dim tic, filename As String
tic = DateTime.Now.Ticks.ToString()
filename = "acctsetup" + tic + ".xls"
oBook.SaveAs(sSampleFolder & filename)
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
My problem here is that I can create the new excel file and save it with the same format(some of the fields are pre-filled), but all the changes I made are nowhere to be found
Can somebody be so kind and tell me what's wrong with my code?
Thanks in advance
Tony
|
|
Reply By:
|
Kieran
|
Reply Date:
|
9/18/2003 11:56:24 PM
|
Untested ... but try
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
' if not on a separate line excel assumes it is a variant
Const sSampleFolder = "F:\ExcelFile\"
set oExcel = CreateObject("Excel.Application")
set oBook = oExcel.Workbooks.Open(sSampleFolder & "accounting.xls")
set oSheet = oBook.Worksheets(1)
set oSheet.Range("D9").Value = "The Graduate School"
set oSheet.Range("D10").Value = "Lawson Accounting Unit"
set oSheet.Range("D11").Value = "Setup & Upload Spreadsheet"
Dim tic, filename As String
tic = DateTime.Now.Ticks.ToString()
filename = "acctsetup" + tic + ".xls"
oBook.SaveAs(sSampleFolder & filename)
set oSheet = Nothing
set oBook = Nothing
set oExcel.Quit()
set oExcel = Nothing
GC.Collect()
' the set command needs to be used liberally with objects. Refer to the help doco.
Hope it helps.
Kieran
|
|
Reply By:
|
xwang4
|
Reply Date:
|
9/19/2003 3:41:40 AM
|
quote:
Originally posted by Kieran
Untested ... but try
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
' if not on a separate line excel assumes it is a variant
Const sSampleFolder = "F:\ExcelFile\"
set oExcel = CreateObject("Excel.Application")
set oBook = oExcel.Workbooks.Open(sSampleFolder & "accounting.xls")
set oSheet = oBook.Worksheets(1)
set oSheet.Range("D9").Value = "The Graduate School"
set oSheet.Range("D10").Value = "Lawson Accounting Unit"
set oSheet.Range("D11").Value = "Setup & Upload Spreadsheet"
Dim tic, filename As String
tic = DateTime.Now.Ticks.ToString()
filename = "acctsetup" + tic + ".xls"
oBook.SaveAs(sSampleFolder & filename)
set oSheet = Nothing
set oBook = Nothing
set oExcel.Quit()
set oExcel = Nothing
GC.Collect()
' the set command needs to be used liberally with objects. Refer to the help doco.
Hope it helps.
Kieran
Thanks a lot
|
|
Reply By:
|
xwang4
|
Reply Date:
|
9/19/2003 1:37:12 PM
|
Dear Kieran
My vs.net will not let me use "set", the error message says that "let" and "set" are no longer supported. Any idea? And one more question, why everytime I save a new .xls file, the system automatically make a copy of the file, and a copy of the new file under F:Thanks
Tony
|
