June 16, 2008 at 2:28 pm
rbarryyoung (6/16/2008)
I would be very interested if you could find a way to make that happen. I haven't tried it in Excel 2007, but in every other version that I have tried, the automatic data updates did overwrites, not inserts.
Try right-clicking in the data range, and looking through "data range properties". I've got Excel 2002 right-now. I get options to either Insert Cells/delete unused, Insert/delete entire rows, or Overwrite. It will also allow me to define line totals which will get copies up or down assuming they're adjacent to the data range.
Note that this deals with rows, NOT columns. Variable columns means SOL I think.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 2:29 pm
rbarryyoung (6/16/2008)
Jeff Moden (6/16/2008)
rbarryyoung (6/16/2008)
Jeff Moden (6/15/2008)
rbarryyoung (6/15/2008)
On the other hand, if your sheets need to be resized based on the number of rows returned, then automated "Get External Data" refreshes can be pretty hard to get working right. I think have VBA code (somewhere) for doing this if you still need it.Nah... sheets resize auto-magically.
But pointers to them (like data ranges) do not.
Actually, I think data-ranges can be made to be self expanding... it's been a very long while since I've had to do something like this so I'll have to try it... not 100% sure about it, yet...
I would be very interested if you could find a way to make that happen. I haven't tried it in Excel 2007, but in every other version that I have tried, the automatic data updates did overwrites, not inserts.
I just tried it using Excel 2003... if you right click on the external data range and select "Data Range Properties", the "Name" that appears is actually a range name and it expands and contracts correctly and automatically when you refresh the external data range. It's selectable from the range pull down and does all of the things you would expect any named range to do as well as being self sized based on the refresh.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2008 at 2:33 pm
Crud... Matt beat me to it... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2008 at 2:50 pm
montgomery johnson (6/16/2008)
I would be interested in anything you've got. There will be future changes on additional spreadsheets.
OK, I checked and unfortunately I do not have much useful stuff that I can share at the moment (customer ownership and confidentialty precludes the specific stuff). Anyway, this is just some common utility functions, it is to Access, instead of SQL, but all you should have to do is change the connection string to fit. And by the way, this being VBA and COM, etc., it uses ADO, not ADO.net.
'APP_DataBase (vba)
'
' This is the module for the APP application's
'Database functions, routines and constants.
'
'2007-06-08 B.Young Created
'
Option Explicit
Public appDB As New ADODB.Connection
Public Function IsOpenDB() As Boolean
' function to track the DB status
IsOpenDB = (appDB.State <> 0) ' 0=closed
End Function
Public Sub CloseDB()
'Close the database if it is open.
If IsOpenDB Then appDB.Close
End Sub
Public Function OpenDB() As Boolean
'Open the Database and indicate if sucessful
Static sPrev As String
Dim sFile As String
If IsOpenDB Then
OpenDB = True 'we are already open
Exit Function
End If
If sPrev = "" Then sPrev = GetSetting(APP_AppName, "History", "DBName")
With Application.FileDialog(msoFileDialogFilePicker)
'specify the file open dialog
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Access Workbooks", "*.mdb"
.Filters.Add "All Files", "*.*"
.InitialFileName = sPrev
.Title = "Open TIP Database"
.Show
If .SelectedItems.Count > 0 Then
sFile = .SelectedItems(1)
Else 'user canceled ...
OpenDB = False
Exit Function
End If
End With
'appDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\byoung\APP\Main.mdb"
appDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile
On Error Resume Next
appDB.Open
If Err.Number <> 0 Then
MsgBox "Error(" & Err.Number & "): " & Err.Description, vbOKOnly + vbCritical, "Error in OpenDB"
OpenDB = False
Exit Function
End If
'Opened ok, so finsh-up and exit
OpenDB = True
sPrev = sFile
SaveSetting APP_AppName, "History", "DBName", sPrev
End Function
Public Function DBLookup(SQLCommand As String, Optional DefaultVal As Variant = "") As Variant
'Execute a SQL statement and return a single value.
Dim rs As Recordset
On Error GoTo ErrHandler 'Handle any/all DB errors
If Not IsOpenDB Then OpenDB
' execute the command
Set rs = appDB.Execute(SQLCommand)
' extract the first field of the first record returned
rs.MoveFirst
DBLookup = rs.Fields(0).Value
Exit Function
ErrHandler:
' whatever the error is, just assign the default value and return
DBLookup = DefaultVal
Exit Function
End Function
Public Sub DBExecute(SQLCommand As String, Optional RecordsAffected As Long = 0)
'Execute a SQL statement that returns no values.
If Not IsOpenDB Then OpenDB
appDB.Execute SQLCommand, RecordsAffected
End Sub
'===================
(edit: added vb formatting tag. Nice!)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 16, 2008 at 3:04 pm
Cool. I stand corrected. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2008 at 6:55 am
Once you have your basic query in place with Import External Data and an ODBC connection, you can use the following VBA code instead, which has one advantage, that of allowing you to change your query on the fly, since it's only a text string ("MyQuery$"):
With Worksheets(1).QueryTables(1)
.Destination = Worksheets(1).Range("A1")
.CommandText = MyQuery$
.Refresh (False)
End With
June 17, 2008 at 8:45 am
Also allows it to possibly be slower because it will recompile every time it's used.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 1:01 pm
Thanks guys. You've given me something verging on excitement.
June 18, 2008 at 4:50 pm
Matt, Jeff:
FYI, I've already used this trick today to put up a "quick & dirty" Service Broker monitor. sweet...
Thanks again,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2008 at 5:13 pm
Now there's a left turn across the field....I've used Excel for a lot of things, but that's a new one for me...:cool:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply