Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Problem when copying a range from closed workbooks (ADO)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Problem when copying a range from closed workbooks (ADO)
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 /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 /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.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default Problem when copying a range from closed workbooks (ADO)
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" wrote:

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 /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 /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.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Problem when copying a range from closed workbooks (ADO)
Ryan,

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

"ryguy7272" wrote:

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" wrote:

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 /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 /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.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default Problem when copying a range from closed workbooks (ADO)
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" wrote:

Ryan,

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

"ryguy7272" wrote:

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" wrote:

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 /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 /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.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Problem when copying a range from closed workbooks (ADO)
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" wrote:

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" wrote:

Ryan,

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

"ryguy7272" wrote:

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" wrote:

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 /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 /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.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying From Closed Workbooks Mike Excel Worksheet Functions 3 September 6th 06 06:33 PM
Copying ranges of cells from closed workbooks? JHongTurney Excel Programming 1 December 5th 05 07:27 PM
Copy a range from closed workbooks (ADO) nc Excel Discussion (Misc queries) 3 October 17th 05 06:02 PM
Problem with named Range in ADO extract from Closed Excel File Dave Bash Excel Programming 1 December 23rd 03 09:10 AM
Copying Data from closed workbooks Kevin G Excel Programming 4 July 31st 03 03:46 PM


All times are GMT +1. The time now is 01:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement