View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Johnslg Johnslg is offline
external usenet poster
 
Posts: 22
Default MS Query "Connections"

Thanks. But I don't have names. I have a querytable connection inside a for
loop:

For Counter = 1 To 100 '....pick a number, I
could probably count the number of populated cells and get an exact #...

With Worksheets("Sheet1")
Result = ActiveCell.Offset(0, 1).Address 'Place return data
one cell to the right of current cell
End With
Worksheets("Sheet1").Range(Result).Clear 'Clear result cell
If MasterAccount = "" Then GoTo NEXTFOR 'Skip empty cells
If MasterAccount = "***" Then Exit For 'End of file token

With Range(Result).Cells 'Format return data
.NumberFormat = "$###,###,###.00"
.Font.Bold = True
.Font.Size = 10
.Font.Color = RGB(75, 0, 255)
.Font.TintAndShade = -0.5
End With

If InStr(MasterAccount, "~") = 0 Then 'check to see if two
account#'s concantenated
FirstAccountNumber = MasterAccount 'single account, set
account number parameter for query
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=LLCSERVER2;UID=john.church;Trusted_C onnection=Yes;APP=2007
Microsoft Office system; " & _
"WSID=JC;DATABASE= & DataB & ",
Destination:=Range(Result), Sql:= _
"Select Sum(AMTRANDTL.AMTRDTranAmt) from AMTRANDTL "
& _
"left outer join GLACCTHDR on GLACCTHDR.GLACHNumber
= AMTRANDTL.GLACHNumber " & _
"left outer join AMTRANHDR on AMTRANHDR.AMTRHNumber
= AMTRANDTL.AMTRHNumber " & _
"where GLACHCode= '" & FirstAccountNumber & "' and
AMTRANHDR.AMTRHPeriod = '" & Period & "' Group By GLACCTHDR.GLACHCode")
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.PreserveColumnInfo = True
.AdjustColumnWidth = False
.Refresh BackgroundQuery:=False
End With

"Don Guillett" wrote:

You may add this to your macro to delete the names before/after your fetch.

For Each Name In Sheets("Data").Names
Name.Delete
Next Name
========
or use a refresh like this with variable interspersed.

With sheets("sheet1").QueryTables(1)
.Connection = _
"URL;http://www.speedtv.com/schedule/index.php?m=&do=&week=" & X &
"&ts=&wholeMonth=&subcat=&program=&usecal=yes&star tMonday=1"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "1,""speedListing"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
=======
or

Sub GP()
With Sheets("sheet1").QueryTables(1)
.Connection = _
"URL;http://postcalc.usps.gov/MailServices.aspx?Country=Domestic&M="
& [mytype] & "&P=0&O=" & [myounces] & "&OZ=78734&DZ=78731"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
--
Don Guillett
SalesAid Software

"Johnslg" wrote in message
...
I have a sheet that lets the user enter a chart of accounts in a column &
then a macro runs against each account (using QueryTable), hits an SQL
database, and returns data. My problem is that it adds a new connection
each
time the mcro runs. I just deleted about 400 connections.

Is there a way to reuse the same connection (it's all going against the
same
server/database) or is there a way to automatically delete connetions?

Thanks.