Posts by XL-Dennis

  • Importing Data

    Re: Importing Data


    Hi all,


    This issue seems to popup on a regular basis so I thought I would make some comments about it.


    # The need
    Before moving ahead the real need needs to be evaluated in terms of it necessary to retrieve all data.


    Per se Excel's workbooks are no good storage place and the aspect of "just in case" do not simple qualify any data to be stored in workbooks.


    What kind of action are You planning to do with the retrieved data?


    # Storage
    It's very easy to suggest to store data in a database, which I nowadays strongly disagree with.


    First of all, it require that we have access to, at least, a so called desktop database.


    Second, it require some basic knowledge how to work with the database.


    Third, a lot of data is already stored in central databases and there is no need to stora the same data in additional local databases unless this is the recommended general approach within the company.


    (Yes, I'm aware of that many end users do not have access to the central databases and also should not for security reasons)

    In my experience the best alternative is simple to store the data in textfiles and retrieve the wanted data for the value added process with ADO/SQL.


    The solution can be quite dynamical if necessary.


    # Use the search function at OzGrid


    Second lesson of today is to assume that the question has already been raised. No issue is per se unique and after nearly 4 years existence (OzGrid) the propability is high that a similar or even an identical question has already been raised.


    Enter 65536 as a search criteria and take part of the output.


    You may also click on the following link:
    http://www.ozgrid.com/forum/sh…p?t=11279&highlight=65536

  • WikiCalc, 12, databases and some tips & tricks

    Hi all,


    I thought it's time to add at least one post before the new year is here.


    WikiCalc & The Web
    The Father of the very first spreadsheet software, VisiCalc, Dan Bricklins, has now launched a new interesting project – WikiCalc, which will bring us one step further to using webbrowsers as an interface and to calculate on the web.


    You can take part of it on the following site: http://danbricklin.com/log/aboutwikicalc01.htm


    Personally, I value his standard of work very highly.


    Unfortunately, the coming Office Live from Microsoft does not include spreadsheeting on the web as the title may indirectly suggest. It seems to set focus only on managing projects and general business information.


    For further information please see: Office Live


    Excel 12
    One important aspect when it comes to new releases of MS Office is that new versions are likely to compete only with older versions of the suite.


    This situation put a high demand on Microsoft to present great news that in return will motivate corporates and organizations to upgrade to the latest available version. Competition is always a great situation for all of us but unfortunately there exists no real competition when it comes to the Office suite.


    Without going into technical details it seems that with the coming release of Office 12, ie; Office 2006, they have managed to create big news and to some extend great news too.


    From what I can see, the next version will change the paradigm on how to work with the softwares in the Office suite and it will probably mean that corporates in the education sector will find a new source for income as well as all the publishers.


    Unlike previous releases, this time Microsoft has adapted the marketing channel blogging to both present all the “whistles and bells” and also by viewing all the news create a growing demand for it. After all, it’s per se a one way communication only.


    One important aspect that will be of interest to follow is if Microsoft will accept and support the new standard OpenDocument format or not.


    However, no matter what, in the end it will be “business as usual” and the Excel community will just add a new version to solve problems with (and create new problems with!).


    If Microsoft really want people to upgrade they must also reconsider the licence agreements as well as the price models.


    Personally, I’ve decided to see Excel as a core presentation layer and therefore I am only interested in the ability to exchange data between Excel and central data storage places.


    For those of You who have not yet found your way to the present blogs about the coming new version of Excel & Office here is a list of the available blogs:
    Microsoft Excel 12
    An Office user interface
    Microsoft Publisher and PDF
    Office XML Formats


    Databases
    The rapid increasing general interest of databases and data storage seems to push several vendors to provide us with free versions of their databases. In my experience the free databases offer us good opportunity to both explore and learn more about them. Of course, one major reason for the vendors to provide us with kind offers is to create a demand to buy their full blown databases but that should not prevent us from testing the offered databases.


    At present some of the largest vendors has recently released new version of their free databases:


    SQL Server 2005 Express Edition
    Oracle 10g Express Edition
    MySQL 5.0


    Recently I was introduced to, by a new friend to me, the free version, FireBird, of Borland’s database, which is an independent project and not under Borland’s control.


    At present I’m trying to evaluate it and for those of You who want to find out more about it please see the following URL: FireBird


    Diversity – take II
    In my previous post I pointed out my support of diversity and as a consequence I recently launched my own Forum. ExcelKB’s forum should be seen as a compliment to OzGrid and other core public Excel forums and Microsoft newsgroups. It sets focus on Best Practical Practice, Database & Excel etc and is focussed on discussion rather then a core Q&A Forum. For more information please see: ExcelKB’s Forum


    Another new friend to me, Bob Philips (aka xld), has recently become a member here at OzGrid. If You are looking for insightful and well written deeper knowledge about Excel then pay a visit to his excellent site and also check out his tools: xlDynamic


    The With Statement
    There are some really important aspects that should force everyone to use the With – End With statement.


    The benefit of the use can be summarized in the following way:


    • A well structured code which makes it easier to read and also to understand.
    • Reduces the number of bound calls which in return makes the code require less memory space and therefore will be running faster.
    • It’s equivalent to assigning the object to a local variable.


    The following is an example of how to use it:


    [vba]
    Option Explicit


    Sub Using_Structured_Approach()
    Dim p_wbTarget As Workbook


    Set p_wbTarget = ActiveWorkbook


    With p_wbTarget.Worksheets(1)
    .Name = "Test"
    With .Range("A1")
    .Font.Bold = True
    .Value = "Hello"
    End With
    With .Range("A2:A10")
    .Value = Application.Transpose(VBA.Array(1, 2, 3, 4, 5, 6, 7, 8, 9))
    .Font.ColorIndex = 3
    End With
    .Protect Password:="Secret"
    End With


    End Sub
    [/vba]


    Using US Keyboard settings
    In Sweden we use a total different kind of keyboard settings & layout (as well as in other countries) compared with the US settings & layout.


    As a challenge for myself I decided to buy a keyboard with US layout and to use the US-settings with the US-version of Windows XP and Office 2003.


    It’s remarkable how hard it is to change the keyboard behavior and I also find it weird to some extent!


    The benefit so far is that I now understand why we use certain buttons in shortcuts and code in most of the Windows-based software.


    Of course, we can always questioning why I do it at all ;)


    The End
    This year has definitely been different compared with many other earlier years for me.


    For some of You it’s business as usual and the speed in life is high while for other it’s time to deal with some difficulties and also perhaps learn more about Yourself.


    For me it has been a time of truly re-evaluation of my life and what is really important to me.


    When I summarize it I find the following “items” are on top of my list:


    • Health
    • Friendship & close and good relations


    It’s very easy to take these two “items” for granted as other “problems” tend to occupy us and keep us “busy”.


    The bottom line (at least for me) is to stop and start to consider and also put time in our health and relations. Without them the life can be very difficult and complicated.


    Finally, I take this opportunity to thank all my friends (You know who You are) in the virtual life from the bottom of my heart and I wish You all a safe and healthy new prosperity new year.

  • Cell Formula to VBA Tool

    Re: Cell Formula to VBA Tool


    Barry,


    You need a reference to Microsoft Forms 2.0 Object Library via Tools | References... in the VB-editor due to the use of DataObject. A "shortcut" is to add a userform to the VB Project and if You not intend to use it then remove it but the reference will remain.

  • Retrieve data from open workbook(s)

    Re: Retrieve data from open workbook(s)


    Hi,


    Thanks for taking Your time and for Your input on this issue :)


    Yes, it seems that by using DAO 3.6 we get a similar memory leak but not necessarily of the same size. Under some circumstances when using ADO the memory in use are doubled while with DAO 3.6 I've so far not experience this large leak.


    What are the outcome of Your findings regarding DAO 3.6?


    With Excel 2003 and with the recent published SP-2 the ADO memory leak still exist.

  • MSSQL with vba connection

    Re: MSSQL with vba connection


    Hi and welcome to OzGrid :)


    Quote


    How do I connect to the database??? do I need to install anything first????


    Below You find a typical connection string:
    [vba]
    Const stConn as String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=IBM"
    [/vba]


    Catalog refer to the database and Data Source to the server (in my case IBM).


    To answer Your question You need to have the MS SQL Server OLE DB Provider available.


    In order to connect to the database You need to use ADO which I'm assume You already know.


    If You have difficulties to create the correct connection string You can download my free add-in that will guide You through steps to get it.


    Available here for download:
    http://www.excelkb.com/article.aspx?id=10061

  • Can't save and restore window state

    Re: Can't save and restore window state


    Bill,


    Events procedure can trick us so therefore keep the number of events procedures down and I still argue that You should only use the Workbook Before_Close ;)


    I usually only use the Form_QueryClose to prevent users to close the form via the X-button.


    To debug and get a better understanding what's going on You should set a breakpoint where You start to manipulate the windows size and use the F8-button to stepwise run the events. In that way You can stepwise evaluate the situation.

  • create several pivot tables from severable data tables automatically

    Re: create several pivot tables from severable data tables automatically


    Hi,


    OK, below You find the skeleton for creating PivotTables:


    [vba]
    Option Explicit


    Sub Create_PTs()
    Dim wbBook As Workbook
    Dim wsSource As Worksheet
    Dim rnSource As Range
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim i As Long, j As Long, lnMaxPT As Long


    Set wbBook = ActiveWorkbook
    Set wsSource = wbBook.Worksheets(1)


    'Number of Pivottables to be generated.
    lnMaxPT = 3


    j = 1


    Application.ScreenUpdating = False


    For i = 1 To lnMaxPT
    With wsSource
    Set rnSource = .Cells(10, j).CurrentRegion
    End With

    Set PTCache = ActiveWorkbook.PivotCaches.Add _
    (SourceType:=xlDatabase, _
    SourceData:=rnSource.Address)

    Set PT = PTCache.CreatePivotTable _
    (TableDestination:=wbBook.Worksheets(2).Cells(10, j), _
    TableName:="PivotTable" & i)

    With PT
    .ManualUpdate = True
    .PivotFields("Period").Orientation = xlRowField
    .PivotFields("Quantity/Order").Orientation = xlDataField
    .ManualUpdate = False
    .ManualUpdate = True
    End With

    j = j + 4

    Next i


    Application.ScreenUpdating = True


    End Sub
    [/vba]

  • Open and write word file with VB

    Re: Open and write word file with VB


    There exist a bunch of Excel-books that cover many things about Excel BUT little about automation of other softwares (open and write date etc). In fact, this is a field which is rarely covered at all in books. Mainly because it does not explicit target the software itself.


    I'm one of "them" who regular manipulate data in other fileformats through automation and therefore You find some stuff on my English site, in the Advanced Excel forum here at OzGrid. BTW, check out the tip of fully automate Office via VB 6.0, which include Excel, Word and Outlook.


    Post Your questions here and I'm totally convinced that You will get a better knowledge and understanding of the answers then buying a book that only give You 5 pages about automation.


    Bob Philips (aka xld) recently post a minor tutorial about early & late binding which You may find useful: http://www.vbaexpress.com/forum/showthread.php?t=5637


    Edit: Here You find a good source for the Windows API ShellExecute:
    http://msdn.microsoft.com/arch…b4/html/msdn_shelexec.asp

  • Open and write word file with VB

    Re: Open and write word file with VB


    Hi,


    I misunderstood what You meant by "open" ;)


    Here is a solution that open a PDF-file:


    [vba]
    Option Explicit


    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long


    Sub Open_PDF()
    ShellExecute 0, "open", "c:\OzGrid.pdf", "", "", 1
    End Sub
    [/vba]

  • Programatically generate a listing of macros

    Re: Programatically generate a listing of macros


    Quote


    But you are saying that there is no programatic way to pull the same information if the workbook is protected?


    Two aspects to pay attention to:


    #1 Protection exist for one reason and when applying the protection then the purpose of its core functionality is achieved, i e to protect the content.


    When applying the protection for VBA then Your protect the VB-project itself.


    #2 Here at OzGrid we don't deal with hacks to get around protection as this is considered to be unethcial.


    Do You still find it strange?