Saving an Excel without dialog box popping up

[VisualBasicHelp]

Hi,

I am trying update an already existing excel file through VB 6.0 . But when I say objExcel.Save, it shows me another dialog box, saying, "this file already exists, whether you want to replace it?"
I don't want this dialog box to appear, it should be saved automatically.

Please let me know, how can I do it.

Many Thanks in advance

 

[hmckillop]

try

Application.displayalerts = False

before you save the file. After saving it, add the line:

Application.displayalerts = True



"I'm living so far beyond my income that we may almost be said to be living apart

 

[HarleyQuinn]

Slightly differently, if you use .SaveAs rather than .Save you can specify in the parameters (ConflictResolution parameter using xlLocalSessionChanges) that you want to accept the local changes without the conflicts dialog.

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VisualBasicHelp]

Hi hmckillop,

I tried your code like the following, but when I open the excel, the value is not getting stored -

Dim xlApp As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim xl As Excel.Workbook
Dim nRow As Integer
Dim flag As Integer

List1.Visible = False
Label1.Visible = False

flag = 0
nRow = 2

'setting the workbook
Set xlBook = xlApp.Workbooks.Open("C:\ISNumber_Details.xls")
'Activating the required sheet
Set xlsheet = xlBook.Sheets(1)

'Search the first column of the sheet whether the entered text exists
While Not IsEmpty(xlsheet.Cells(nRow, 1))
' Process the cell here
If UCase(Text1.Text) = UCase(xlsheet.Cells(nRow, 1)) Then
flag = 1
List1.AddItem (xlsheet.Cells(nRow, 2))
End If
' Update also nRow and/or nCol
nRow = nRow + 1
Wend

'Entered text is found
If flag = 1 Then
Label1.Caption = Text1.Text & " Already Exists in the Following studies"
Label1.Visible = True
List1.Visible = True
'Entered text is not found
Else
'Store the value in the next row
xlsheet.Cells(nRow, 1).Value = UCase(Text1.Text)
Application.DisplayAlerts = False
'Save the book
xlBook.Save
Application.DisplayAlerts = True
'Close the book
xlBook.Close
'Exit the application
xlApp.Quit

End If

 

[HarleyQuinn]

The Application object in this case is your Excel Application object, so you would need to use:

xlApp.DisplayAlerts...

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VisualBasicHelp]

Thanks Harley,
I changed Application to xlApp, but still the value is not getting stored in excel.

 

[HarleyQuinn]

And the text in Text1 definitely doesn't appear in the first column of the Excel sheet?

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VisualBasicHelp]

I have put a message box before closing the excel to show the value in that cell, at that time it correctly pops up the value. When I open the excel sheet manually, it has not been reflected there.

 

[HarleyQuinn]

That's a little strange, seems to work as you intend on the sheet I'm testing on.

One thing I would add (though this won't solve this particular problem) is that in the branch of the If statement that fires if flag = 1 you don't clear up your Excel objects and that may cause you other problems in the future.

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VisualBasicHelp]

To clear up the objects, I have added the code. Now this is my full set code-

Dim xlApp As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim xl As Excel.Workbook
Dim nRow As Integer
Dim flag As Integer

List1.Visible = False
Label1.Visible = False

flag = 0
nRow = 2

'setting the workbook
Set xlBook = xlApp.Workbooks.Open("C:\ISNumber_Details.xls")
'Activating the required sheet
Set xlsheet = xlBook.Sheets(1)

'Search the first column of the sheet whether the entered text exists
While Not IsEmpty(xlsheet.Cells(nRow, 1))
' Process the cell here
If UCase(Text1.Text) = UCase(xlsheet.Cells(nRow, 1)) Then
flag = 1
List1.AddItem (xlsheet.Cells(nRow, 2))
End If
' Update also nRow and/or nCol
nRow = nRow + 1
Wend

'Entered text is found
If flag = 1 Then
Label1.Caption = Text1.Text & " Already Exists in the Following studies"
Label1.Visible = True
List1.Visible = True
'Entered text is not found
Else
'Store the value in the next row
xlsheet.Cells(nRow, 1).Value = UCase(Text1.Text)
xlApp.DisplayAlerts = False
'Save the book
xlBook.Save
xlApp.DisplayAlerts = True

'Close the book
'xlBook.Close
'Exit the application
'xlApp.Quit
Cleanup:
Set xlsheet = Nothing
If Not xlBook Is Nothing Then xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

End If

 

[VisualBasicHelp]

Thanks . It worked. I tried with another excel sheet, it was working. There might be some problem with the existing excel sheet, now I have to find out that.

Thanks Harley

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com