Problem in Form unload procedure.

[ajay_vb]

Friends,
I am using the code as below for exporting and importing data from an excel file to a Flexgrid.The problem i have is in Form unload procedure.If i use "Save" as in line "m_oXlWb.Close SaveChanges:=True", i get a pop up message asking for saving into copy of flexgrid file.If i use "Save as" as in line
" m_oXlWb.SaveAs 'FileName:=m_csFileName" i get message asking whether to replace existing flexgrid.xls file.I want the save to happen to same file without replacing every time and without giving any popup messages.Please help me .It's very urgent.Thanks in advance.


Private Const m_csFileName As String = "D:\FlexGridData.xls"
Private Const m_clCols As Long = 5
Private Const m_clRows As Long = 21

Private m_oXlApp As Object
Private m_oXlWb As Object
Private m_oXlWs As Object

Private Sub Form_Load()
Dim intLoopIndex As Integer
Dim vData As Variant
Dim lRow As Long, lCol As Long

With MSFlexGrid1
.Cols = 5
.Row = 0
.Col = 1
.Text = "Text1"
.Col = 2
.Text = "Text2"
.Col = 3
.Text = "Text3"
.Col = 4
.Text = "Text4"
End With

MSFlexGrid1.Rows = 20
For intLoopIndex = 1 To MSFlexGrid1.Rows - 1
MSFlexGrid1.Col = 0
MSFlexGrid1.Row = intLoopIndex
MSFlexGrid1.Text = "SAVE " & Str(intLoopIndex)
Next intLoopIndex



Set m_oXlApp = CreateObject("Excel.Application")
Set m_oXlWb = m_oXlApp.Workbooks.Open(FileName:=m_csFileName)
Set m_oXlWs = m_oXlWb.ActiveSheet
Set vData = m_oXlWs.Cells(1, 1).Resize(m_clRows, m_clCols)

m_oXlApp.Visible = False

With MSFlexGrid1
.FixedRows = 1
.FixedCols = 0

.Rows = m_clRows + 1
.Cols = m_clCols

For lRow = 1 To m_clRows
For lCol = 2 To m_clCols
.TextMatrix(lRow, lCol - 1) = vData(lRow, lCol)
Next lCol
Next lRow

End With

m_oXlWb.Close SaveChanges:=False
m_oXlApp.Quit

Set m_oXlWs = Nothing
Set m_oXlWb = Nothing
Set m_oXlApp = Nothing
End Sub
Private Sub Form_Unload()
Dim i As Long
Dim p As Long
Dim newCell As String

Set m_oXlApp = CreateObject("Excel.Application")
Set m_oXlWb = m_oXlApp.Workbooks.Open("D:\FlexGridData.xls")
Set m_oXlWs = m_oXlWb.ActiveSheet

m_oXlApp.Visible = False

For i = 1 To MSFlexGrid1.Cols - 1
For p = 1 To MSFlexGrid1.Rows - 1
MSFlexGrid1.Row = p
MSFlexGrid1.Col = i
newCell = Chr(i + 64) & p
m_oXlWb.Worksheets(1).Range(newCell).Value = MSFlexGrid1.Text
Next
Next
'm_oXlWb.Close SaveChanges:=True
m_oXlWb.SaveAs 'FileName:=m_csFileName

m_oXlApp.Quit

Set m_oXlWs = Nothing
Set m_oXlWb = Nothing
Set m_oXlApp = Nothing

Unload Form1

End Sub

[Atom290]

Hiya Ajay

You really need to get away from this idea of putting each of the data cells in the flexgrid into an excel cell. It is very bad practice!

You are moving items around in memory way too much and it will be time consuming to say nothing of the least.

The best way, and I can only speak from working with huge data feeds, is to create an array as I gave you. Work within VB to do the majority of effort and then farm it out to the excel sheet once you have all of the data. It will be far more reliable and wont require you to have to use fancy methods of working out the name of the cell. It is good to experiment I do agree, but trust me on this one....it is the most satisfying way of just put the data from array into the excel spreadsheet in one line. Sometimes less is more The other reason, is that if the model was to expand...you will need to write a much longer algorithm to cope with the AA, AB, AC etc

If you are going to open and close the spreadsheet for each module then you don't need to declare the objects at a modular level....just procedural.

How long is the form going to be open for? If it isn't a long time then why shut Excel down? At the very least keep the App alive, and if you want to close the workbook down then fair enough....but unless someone else will need access to it, then don't bother.

So my advice would be to open up the excel objects at the top of the load procedure, and close the excel objects at the bottom of the unload.

You don't need to add the visible=false in as it is non-visible by default. I only stuck it there so when testing, if it fails, you don't end up with 30 instances of excel in memory.

At the beginning use the TextMatrix to set the top line of your FlexGrid, rather than set each col number. If you were setting colours for the cells then you would have no choice, but it will be less code, also the next lines are using the FlexGrid, but you have left them out of the with statement.

With regards to the save...you arent being consistent with your open statement. Use the const m_csFileName you set up throughout. The reason I used the SaveChanges:=True is at the end, it closes down the workbook and saves any changes made to the sheet. You are keeping the same workbook and effectively using it as a data source. So there is no need to change the name or location of the workbook?

By using the saveas...it will prompt a question as you are now overwriting an existing workbook, albeit one you have open! In this instance there is no need to use this method...but otherwise if this was causing a problem you would use the application.displayalerts=false. That would suppress the warning.

Sorry to rip into your code....it isn't intentional...it good to experiment otherwise you don't know what does and doesn't work!

Good luck!

[ajay_vb]

Thanks you Mr.Atom 290 for your valuable suggestions.I will consider these suggestions seriously and i will try doing changes as you said.Bye.