Query Table problem

  • Thread starter Thread starter Trev
  • Start date Start date
T

Trev

When I try to execute the code below, instead of the query table being
returned I get a message in row A1 of stating "ExternalData_3: Getting Data
...."

This problem seems to be caused by the Call to the DeleteAllNames module (if
I remove this line the query table is returned). Is there any way I can
return the query table AND delete all the names by just having one macro
call the other.

Thanks in advance,
Trev.

Sub GetInfo()
Dim sqlstring As String
Dim connstring As String
Sheets("Sheet1").Select
Columns("A:E").Select
Selection.Delete Shift:=xlToLeft
sqlstring = _
"SELECT tblAlbums.Artist, tblAlbums.Title," & Chr(13) & Chr(10) & _
"tblTracks.TrackID, tblTracks.Title, tblTracks.Duration" & Chr(13) &
"" & Chr(10) & _
"FROM tblAlbums, tblArtists, tblTracks" & Chr(13) & "" & Chr(10) & _
"WHERE tblAlbums.AlbumID = tblTracks.AlbumID" & Chr(13) & Chr(10) &
_
"AND tblAlbums.ArtistID = tblArtists.ArtistID" & Chr(13) & Chr(10) &
_
"AND tblArtists.ArtistID = tblTracks.ArtistID" & Chr(13) & Chr(10) &
_
"ORDER BY tblAlbums.Artist,tblAlbums.Title,tblTracks.TrackID"
connstring = _
"ODBC;DSN=MS Access Database;" & Chr(13) & Chr(10) & _
"DBQ=C:\Program Files\Music\Music.mdb;" & Chr(13) & Chr(10) & _
"DefaultDir=C:\Program Files\Music;DriverId=264;FIL=MS Acc"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"), Sql:=sqlstring)
.Name = ""
.Refresh
End With
Call DeleteAllNames 'problem occurs here
End Sub

Sub DeleteAllNames()
Dim nam As Excel.Name
For Each nam In ActiveWorkbook.Names
nam.Delete
Next
End Sub
 
This simple one works for me as written. Try it after your end with
Change sheet name or change to in activesheet.names

For Each Name In Sheets("Data").Names
Name.Delete
Next Name
 
Back
Top