Problem when copying a range from closed workbooks (ADO)

A

avardaneg

I'm trying to use your Ron de Bruin's code to copy a range from a closed
workbook stored in a FTP location (http://www.rondebruin.nl/ado.htm), it was
working fine yesterday but today all of a sudden I started to have problems
with it. I was wondering you would have some clues of what's causing the
problem:

This is my code:
Sub get_wmreport()

Dim act As Worksheet
Dim currdate As Date
Dim newdate As Date

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set act = ThisWorkbook.Sheets("WM_Report")
currdate = act.Cells(2, 13)
GetRange "ftp://ftp_user:[email protected]/Daily-EC-Active", "Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", "M2:M2", _
act.Range("M2:M2")
newdate = act.Cells(2, 13)
If newdate = currdate Then GoTo skip1:
act.Activate
act.Cells(5, 1).AutoFilter
act.Cells(5, 1).AutoFilter
act.Range("A6:p60000").ClearContents
GetData "ftp://ftp_user:[email protected]/Daily-EC-Active/Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", _
"A6:p60000", act.Range("A6:p60000"), False, False
If IsEmpty(act.Cells(6, 1)) Then
act.Rows(6).Delete
End If

skip1:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub


This is Ron's "GetData" code:
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
SourceRange As String, TargetRange As Range, Header As
Boolean, UseHeaderRow As Boolean)
' 30-Dec-2007, working in Excel 2000-2007
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim lCount As Long

' Create the connection string.
If Header = False Then
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If

If SourceSheet = "" Then
' workbook level name
szSQL = "SELECT * FROM " & SourceRange$ & ";"
Else
' worksheet level name or range
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
End If

On Error GoTo SomethingWrong

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

' Check to make sure we received data and copy the data
If Not rsData.EOF Then

If Header = False Then
TargetRange.Cells(1, 1).CopyFromRecordset rsData
Else
'Add the header cell in each column if the last argument is True
If UseHeaderRow Then
For lCount = 0 To rsData.Fields.Count - 1
TargetRange.Cells(1, 1 + lCount).Value = _
rsData.Fields(lCount).Name
Next lCount
TargetRange.Cells(2, 1).CopyFromRecordset rsData
Else
TargetRange.Cells(1, 1).CopyFromRecordset rsData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing
Exit Sub

SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " &
SourceFile, _
vbExclamation, "Error"
On Error GoTo 0

End Sub

It works fine the first time I run the macro, but if I run it again right
after the first time it errors out:
"The file name, Sheet name or Range is invalid of... "

If I close the workbook and Excel, re-open and run the code again it works.
I have also noticed that when I step through the "GetData" code it seems to
be hanging a long time at the following line:

rsCon.Close

Any help would be greatly appreciated.
 
R

ryguy7272

Can you run this little snipped of code right at the beginning of your macro?

Application.ScreenUpdating = False
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Application.ScreenUpdating = True


Test on a copy, or some kind of backup, of your Workbook so you can see what
it does... Unintended results can be a total PITA...

Regards,
Ryan---

--
RyGuy


avardaneg said:
I'm trying to use your Ron de Bruin's code to copy a range from a closed
workbook stored in a FTP location (http://www.rondebruin.nl/ado.htm), it was
working fine yesterday but today all of a sudden I started to have problems
with it. I was wondering you would have some clues of what's causing the
problem:

This is my code:
Sub get_wmreport()

Dim act As Worksheet
Dim currdate As Date
Dim newdate As Date

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set act = ThisWorkbook.Sheets("WM_Report")
currdate = act.Cells(2, 13)
GetRange "ftp://ftp_user:[email protected]/Daily-EC-Active", "Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", "M2:M2", _
act.Range("M2:M2")
newdate = act.Cells(2, 13)
If newdate = currdate Then GoTo skip1:
act.Activate
act.Cells(5, 1).AutoFilter
act.Cells(5, 1).AutoFilter
act.Range("A6:p60000").ClearContents
GetData "ftp://ftp_user:[email protected]/Daily-EC-Active/Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", _
"A6:p60000", act.Range("A6:p60000"), False, False
If IsEmpty(act.Cells(6, 1)) Then
act.Rows(6).Delete
End If

skip1:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub


This is Ron's "GetData" code:
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
SourceRange As String, TargetRange As Range, Header As
Boolean, UseHeaderRow As Boolean)
' 30-Dec-2007, working in Excel 2000-2007
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim lCount As Long

' Create the connection string.
If Header = False Then
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If

If SourceSheet = "" Then
' workbook level name
szSQL = "SELECT * FROM " & SourceRange$ & ";"
Else
' worksheet level name or range
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
End If

On Error GoTo SomethingWrong

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

' Check to make sure we received data and copy the data
If Not rsData.EOF Then

If Header = False Then
TargetRange.Cells(1, 1).CopyFromRecordset rsData
Else
'Add the header cell in each column if the last argument is True
If UseHeaderRow Then
For lCount = 0 To rsData.Fields.Count - 1
TargetRange.Cells(1, 1 + lCount).Value = _
rsData.Fields(lCount).Name
Next lCount
TargetRange.Cells(2, 1).CopyFromRecordset rsData
Else
TargetRange.Cells(1, 1).CopyFromRecordset rsData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing
Exit Sub

SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " &
SourceFile, _
vbExclamation, "Error"
On Error GoTo 0

End Sub

It works fine the first time I run the macro, but if I run it again right
after the first time it errors out:
"The file name, Sheet name or Range is invalid of... "

If I close the workbook and Excel, re-open and run the code again it works.
I have also noticed that when I step through the "GetData" code it seems to
be hanging a long time at the following line:

rsCon.Close

Any help would be greatly appreciated.
Click to expand...
 
A

avardaneg

Ryan,

I tried your suggestion but it returned a run-time error...
thanks anyway.

ryguy7272 said:
Can you run this little snipped of code right at the beginning of your macro?

Application.ScreenUpdating = False
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Application.ScreenUpdating = True


Test on a copy, or some kind of backup, of your Workbook so you can see what
it does... Unintended results can be a total PITA...

Regards,
Ryan---

--
RyGuy


avardaneg said:
I'm trying to use your Ron de Bruin's code to copy a range from a closed
workbook stored in a FTP location (http://www.rondebruin.nl/ado.htm), it was
working fine yesterday but today all of a sudden I started to have problems
with it. I was wondering you would have some clues of what's causing the
problem:

This is my code:
Sub get_wmreport()

Dim act As Worksheet
Dim currdate As Date
Dim newdate As Date

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set act = ThisWorkbook.Sheets("WM_Report")
currdate = act.Cells(2, 13)
GetRange "ftp://ftp_user:[email protected]/Daily-EC-Active", "Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", "M2:M2", _
act.Range("M2:M2")
newdate = act.Cells(2, 13)
If newdate = currdate Then GoTo skip1:
act.Activate
act.Cells(5, 1).AutoFilter
act.Cells(5, 1).AutoFilter
act.Range("A6:p60000").ClearContents
GetData "ftp://ftp_user:[email protected]/Daily-EC-Active/Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", _
"A6:p60000", act.Range("A6:p60000"), False, False
If IsEmpty(act.Cells(6, 1)) Then
act.Rows(6).Delete
End If

skip1:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub


This is Ron's "GetData" code:
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
SourceRange As String, TargetRange As Range, Header As
Boolean, UseHeaderRow As Boolean)
' 30-Dec-2007, working in Excel 2000-2007
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim lCount As Long

' Create the connection string.
If Header = False Then
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If

If SourceSheet = "" Then
' workbook level name
szSQL = "SELECT * FROM " & SourceRange$ & ";"
Else
' worksheet level name or range
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
End If

On Error GoTo SomethingWrong

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

' Check to make sure we received data and copy the data
If Not rsData.EOF Then

If Header = False Then
TargetRange.Cells(1, 1).CopyFromRecordset rsData
Else
'Add the header cell in each column if the last argument is True
If UseHeaderRow Then
For lCount = 0 To rsData.Fields.Count - 1
TargetRange.Cells(1, 1 + lCount).Value = _
rsData.Fields(lCount).Name
Next lCount
TargetRange.Cells(2, 1).CopyFromRecordset rsData
Else
TargetRange.Cells(1, 1).CopyFromRecordset rsData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing
Exit Sub

SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " &
SourceFile, _
vbExclamation, "Error"
On Error GoTo 0

End Sub

It works fine the first time I run the macro, but if I run it again right
after the first time it errors out:
"The file name, Sheet name or Range is invalid of... "

If I close the workbook and Excel, re-open and run the code again it works.
I have also noticed that when I step through the "GetData" code it seems to
be hanging a long time at the following line:

rsCon.Close

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
R

ryguy7272

Most of the code seems fine, but I can't follow all of it. Have you ever
seen this?
http://www.rondebruin.nl/ado.htm


Regards,
Ryan---

--
RyGuy


avardaneg said:
Ryan,

I tried your suggestion but it returned a run-time error...
thanks anyway.

ryguy7272 said:
Can you run this little snipped of code right at the beginning of your macro?

Application.ScreenUpdating = False
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Application.ScreenUpdating = True


Test on a copy, or some kind of backup, of your Workbook so you can see what
it does... Unintended results can be a total PITA...

Regards,
Ryan---

--
RyGuy


avardaneg said:
I'm trying to use your Ron de Bruin's code to copy a range from a closed
workbook stored in a FTP location (http://www.rondebruin.nl/ado.htm), it was
working fine yesterday but today all of a sudden I started to have problems
with it. I was wondering you would have some clues of what's causing the
problem:

This is my code:
Sub get_wmreport()

Dim act As Worksheet
Dim currdate As Date
Dim newdate As Date

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set act = ThisWorkbook.Sheets("WM_Report")
currdate = act.Cells(2, 13)
GetRange "ftp://ftp_user:[email protected]/Daily-EC-Active", "Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", "M2:M2", _
act.Range("M2:M2")
newdate = act.Cells(2, 13)
If newdate = currdate Then GoTo skip1:
act.Activate
act.Cells(5, 1).AutoFilter
act.Cells(5, 1).AutoFilter
act.Range("A6:p60000").ClearContents
GetData "ftp://ftp_user:[email protected]/Daily-EC-Active/Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", _
"A6:p60000", act.Range("A6:p60000"), False, False
If IsEmpty(act.Cells(6, 1)) Then
act.Rows(6).Delete
End If

skip1:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub


This is Ron's "GetData" code:
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
SourceRange As String, TargetRange As Range, Header As
Boolean, UseHeaderRow As Boolean)
' 30-Dec-2007, working in Excel 2000-2007
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim lCount As Long

' Create the connection string.
If Header = False Then
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If

If SourceSheet = "" Then
' workbook level name
szSQL = "SELECT * FROM " & SourceRange$ & ";"
Else
' worksheet level name or range
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
End If

On Error GoTo SomethingWrong

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

' Check to make sure we received data and copy the data
If Not rsData.EOF Then

If Header = False Then
TargetRange.Cells(1, 1).CopyFromRecordset rsData
Else
'Add the header cell in each column if the last argument is True
If UseHeaderRow Then
For lCount = 0 To rsData.Fields.Count - 1
TargetRange.Cells(1, 1 + lCount).Value = _
rsData.Fields(lCount).Name
Next lCount
TargetRange.Cells(2, 1).CopyFromRecordset rsData
Else
TargetRange.Cells(1, 1).CopyFromRecordset rsData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing
Exit Sub

SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " &
SourceFile, _
vbExclamation, "Error"
On Error GoTo 0

End Sub

It works fine the first time I run the macro, but if I run it again right
after the first time it errors out:
"The file name, Sheet name or Range is invalid of... "

If I close the workbook and Excel, re-open and run the code again it works.
I have also noticed that when I step through the "GetData" code it seems to
be hanging a long time at the following line:

rsCon.Close

Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
A

avardaneg

That's exactly where I got the "GetData" code from... I sent him a msg asking
for some advice but got no replies yet, I was hoping to find somebody else in
this forum that has gotten similar issues and could help me out.

ryguy7272 said:
Most of the code seems fine, but I can't follow all of it. Have you ever
seen this?
http://www.rondebruin.nl/ado.htm


Regards,
Ryan---

--
RyGuy


avardaneg said:
Ryan,

I tried your suggestion but it returned a run-time error...
thanks anyway.

ryguy7272 said:
Can you run this little snipped of code right at the beginning of your macro?

Application.ScreenUpdating = False
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Application.ScreenUpdating = True


Test on a copy, or some kind of backup, of your Workbook so you can see what
it does... Unintended results can be a total PITA...

Regards,
Ryan---

--
RyGuy


:

I'm trying to use your Ron de Bruin's code to copy a range from a closed
workbook stored in a FTP location (http://www.rondebruin.nl/ado.htm), it was
working fine yesterday but today all of a sudden I started to have problems
with it. I was wondering you would have some clues of what's causing the
problem:

This is my code:
Sub get_wmreport()

Dim act As Worksheet
Dim currdate As Date
Dim newdate As Date

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set act = ThisWorkbook.Sheets("WM_Report")
currdate = act.Cells(2, 13)
GetRange "ftp://ftp_user:[email protected]/Daily-EC-Active", "Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", "M2:M2", _
act.Range("M2:M2")
newdate = act.Cells(2, 13)
If newdate = currdate Then GoTo skip1:
act.Activate
act.Cells(5, 1).AutoFilter
act.Cells(5, 1).AutoFilter
act.Range("A6:p60000").ClearContents
GetData "ftp://ftp_user:[email protected]/Daily-EC-Active/Daily EC
Metric Report_-_Active on NAP.xls", "Sheet1", _
"A6:p60000", act.Range("A6:p60000"), False, False
If IsEmpty(act.Cells(6, 1)) Then
act.Rows(6).Delete
End If

skip1:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub


This is Ron's "GetData" code:
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
SourceRange As String, TargetRange As Range, Header As
Boolean, UseHeaderRow As Boolean)
' 30-Dec-2007, working in Excel 2000-2007
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim lCount As Long

' Create the connection string.
If Header = False Then
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If

If SourceSheet = "" Then
' workbook level name
szSQL = "SELECT * FROM " & SourceRange$ & ";"
Else
' worksheet level name or range
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
End If

On Error GoTo SomethingWrong

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

' Check to make sure we received data and copy the data
If Not rsData.EOF Then

If Header = False Then
TargetRange.Cells(1, 1).CopyFromRecordset rsData
Else
'Add the header cell in each column if the last argument is True
If UseHeaderRow Then
For lCount = 0 To rsData.Fields.Count - 1
TargetRange.Cells(1, 1 + lCount).Value = _
rsData.Fields(lCount).Name
Next lCount
TargetRange.Cells(2, 1).CopyFromRecordset rsData
Else
TargetRange.Cells(1, 1).CopyFromRecordset rsData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing
Exit Sub

SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " &
SourceFile, _
vbExclamation, "Error"
On Error GoTo 0

End Sub

It works fine the first time I run the macro, but if I run it again right
after the first time it errors out:
"The file name, Sheet name or Range is invalid of... "

If I close the workbook and Excel, re-open and run the code again it works.
I have also noticed that when I step through the "GetData" code it seems to
be hanging a long time at the following line:

rsCon.Close

Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Follow up question. Get value of closed file. 5
SQL String 1
ADO access to excel workbook on website 1
SQL not importing records from ACCDB 2
Compile Error (ADO) Ron De Bruin 2
Error With ADO (Starting to pull my hair out) 2
Copy Data from Excel 2007 XLSM workbook to Excel 2003 XLS workbook using ADO 2
TO ARM REPORT FROM BOOKS CLOSED - ADO 0

Top