Writing to Excel
I have a small problem. I have a function that would create an excel file( CreateExcelFileAndFields) I am trying to pass parameters to it so it would write into the excel sheet in specific location. But if I call the fuction again, it would delete what it has written in thefirst time it was called and then write the second value of the second time it was calling
Here is what I mean
Dim Display, Avg
‘let’s say that Display is
Display=”Here is the test”
What it does, if I put a breakpoint on the second call [Call CreateExcelFileAndFields(Filepath,2,2,Avg)] and run the script, the Display value would be in cell 2,1. If I run both call function, I get only the second value (Avg) and the Display value is gone. Here is my create excel file function:
Public Function CreateExcelFileAndFields (parFileName,Xparm,Yparm,Result)
Dim strExcelPath, objExcel, objSheet,intRow,blnExhausted,strFirstRow,strMyColu mns,strMyCells
'''send the input param parFileName to loc var
strExcelPath = parFileName
Set objExcel = CreateObject("Excel.Application")
On error resume next
'''' Create the fields
objExcel.Cells(1,1).Value = "Test Description"
objExcel.Cells(1,2).Value = "Hi"
objExcel.Cells(1,3).Value = "Hello"
'''Make the colums centered , First row Bold and with red color
Set strFirstRow =objExcel.Rows("1:1")
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 14
.Color = 255
.TintAndShade = 0
'''''''''''' Set the column property
set strMyColumns =objExcel.Columns("A:R")
'.HorizontalAlignment = xlCenter
'.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = -5002
'.ReadingOrder = xlContext
.MergeCells = False
.autofit = True
Set objWs = Nothing
objExcel.DisplayAlerts = False
Set objExcel = Nothing
I would really appreciate it if you could help me out. I know it is a small silly thing, which I can’t catch.
Re: Writing to Excel
Just like your function name says, you create a new excel file with each call and then save it as the given filename. So on the second call you are overwriting the first if you pass the same path.
You need an EditExcelFileAndFields function where you would open and edit a known pre-existing file. [img]/images/graemlins/wink.gif[/img]
Or modify this one to check to see if the file already exists, and if it does open that instead of creating a new workbook.
Re: Writing to Excel