Disable Data Connection

R

Roopa

Hi,
I am using Activeworkbook.Refreshall in one of my functions.
But when I go offline its displaying dialog box "Microsoft Access Database
Engine" to connect again.
But I want to disable the dialog box or close the connection if I go offline.
Can anybody let me know how i can do this?
Thanks.
 
M

Michael

A simple solution could be to copy paste values to a different worksheet at
the end of your subroutine.
Or maybe
Application.DisplayAlerts = False
based on a paritcular event, on Exit
 
R

Roopa

Hi Michael,
Thanks for the reply.
But I tried Application.DisplayAlerts = False
but its not working.
And about copy paste values,
I have connection to Sharepoint site in which my Budget file is resided.
I get data from budget file and if any changes in the file it should reflect
in my excel workbook.
Can you please tell me anyway I can achieve this.
Thanks alot.
 
M

Michael

This a generic copying subroutine posted in this discussion group a while
ago, but i should do the trick, you must tweak it, it uses column A if you
want a different column change the number 1 on the following line to the
column number you need
sh1.Cells(i,1).Text & " " & sh1.cells(i,2).Text
and change the sheet names to suit your needs.

Sub copydata()
Dim sh1 as worksheet, sh2 as worksheet
Dim numrows as string
sh1 = Workbooks("Book1.xls").Worksheets("sheet1")
sh2 = Workbooks("Book2.xls").Worksheets("Sheet1")
numrows = Application.InputBox("Enter number of rows")
if isnumber(numrows) then
for i = 1 to int(numrows)
sh2.Cells(i,1) = sh1.Cells(i,1).Text & " " & sh1.cells(i,2).Text
next
end if
End sub


--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.
 
Top