Posts by BubbasMom2

    Hi, Kenneth

    Thank you for your helpful suggestions. I've been tinkering with this project for a while now, and finally decided to "merge" my original code with code snippets gleaned from OzGrid and elsewhere. Your earlier post on checking for existing "same name" pdf's was certainly helpful in that regard! The pdf is created once the user completes a flight evaluation that takes about 5 minutes... and nobody wants to invest even that amount of time period something that won't save the evaluation results, so - among three different macros - my spreadsheet will generate pdf, or xps electronic copies when possible, a Word document if necessary, or - if all else fails - send the pertinent workbook pages to the printer. As of today, the code below accomplishes the electronic part of that chain, with a filename that lists the pilot's last name, the date of flight DD-MMM-YYY and type of evaluation ("Declared" or "Free", depending on the user-select analysis.) If a pdf or xps file by the exact same name is already on the desktop, the macro "proposes" a pdf file name the user must either use or change, as he sees fit... (By default, the pdf or xps file is headed for the desktop, and existing files are listed in the pertinent application's "save as" window, so the user can make an informed decision!) Once a valid filename is entered, Excel closes, and in the case of pdf or xps documents, it remains open for the user to review.

    I have commented out the Excel workbook page names that will be used. For my test purposes, I've been testing this code in a smaller workbook with only 2 pages.

    ....MORE.... What's happening now is (1) if a same name pdf exists on the desktop, it is not replaced by the new pdf - the prior copy remains intact. In MOST cases, the cell valus at E33 should be just dandy, because it addresses the two major types of evaluation the spreadsheet performs. BUT (2) I can't write a macro for a data entry error the user discovers only after the pdf is created... Again: thanks for your patience!

    OK...let me start over. Below is the entire Module now working in US Excel. I thought the "Public Function" would apply to all macros in this module, which is why - in "ElecCopy"- I commented out an earlier line using the dreaded English name for USERS desktop... What I'd also like to do is ADD a requirement that if a pdf by the same name as specified at E33 is already on the desktop, there's a minimized pdf window OPEN on the desktop, awaiting a name the user must type in. (That's the way things went before I got the bright idea to base filename on a cell value...) Sorry for the confusion!

    Hi, Roy

    I'm not getting any error messages, but one of my German users can't generate pdf or xps... he's no expert, but looked at the macro and as he described it: It looks as if the desktop on my system (it has German WIN7 installed) is "C:\Benutzer\Howard\Desktop" (Hmmm Howard is his first name... how to anticipate that!?)

    Now that I look at it, it also seems the "Get Desktop" reference in the "Print This" macro doesn't carry through to (or is being overridden by) the following two lines in "ElecCopy":


    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Owner\Desktop" & Range("E33").Text & ".pdf", OpenAfterPublish:=True
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypeXPS, Filename:="C:\Users\Owner\Desktop" & Range("E33").Text & ".xps", OpenAfterPublish:=True


    Any ideas?


    Hi, All

    I've run into a problem with saving evaluation results in pdf, xps, word, or sending to the printer....apparently, German Excel speaks...uhm...German(!) at least in terms of defining the "User" desktop...The attached related macros are ugly, but the intent is to generate results in any of four types. The pdf & xps options have recently been modified to specify file name based on cell value at E33 of the "Print This" worksheet. (In a perfect world, I would like the "electronic" types to include a warning if a file by the same name exists.)

    Hi, Sami

    You and I exchanged a few e-mails a couple of years ago... now, after revising code per OzGrid suggestions, I'd like to have someone with 64-bit Excel test my revisions. (The earlier responder had a 64-bit SYSTEM running 32-bit Excel... no help at all!!

    Are you interested?


    Revisions made to long-standing linked workbooks originally written for 32-bit Excel, modified per OzGrid and other advisors to run in both 32- and 64-bit versions of Excel. If everything's OK, this should take 15 minutes!! If everything is NOT OK, please advise and submit an estimate of additional payment necessary to make revisions.

    Will provide more information to anyone interested! (In the meantime, shows what the linked 32-bit workbooks do...


    I'm confused on whether/how much of the following 3 macros will need revision to work in both 32- and 64-bit Excel 2010 & later. If revision is necessary, please suggest conditional wording to accomplish this!(These macros provide for users to save evaluation results on the desktop in PDF, XPS or WORD, send to an installed printer's queue... or - if all else fails - take screen shots[!])


    Hi, All

    As I understand it, most of my code written in 32-bit 2007 & 2010 Excel should work in 64-bit versions of same, as well as 2013 & newer. Does either of the following require rewording? (I so, please suggest conditional wording to address 32 & 64-bit variants.)


    [/COLOR][/SIZE][/FONT]Option ExplicitPrivate Declare Function InternetGetConnectedStateEx Lib "wininet.dll" (ByReflpdwFlags As Long, ByVal lpszConnectionName As String, ByVal dwNameLen AsInteger, ByVal dwReserved As Long) As LongDim sConnType AsString * 255……(other stuff happens)...Dim Ret As LongRet =InternetGetConnectedStateEx(Ret, sConnType, 254, 0)

    CODE 2: (Hmmm... this isn't a "declare" statement per se...)

    Option ExplicitPublic Function GetDesktop() As String    GetDesktop =CreateObject("WScript.Shell").SpecialFolders("Desktop")& _        Application.PathSeparatorEnd Function

    Thanks in advance for any/all help!

    Judy R


    Great help on the free forum... but I need a bit more to update my project for compatibility with both 32- and 64-bit versions of Excel 2013. The project involves five linked workbooks, and four include annotations made by the VBA Compatibility Inspector. (It found a total of 191 items: 55 "deprecated"; 118 "changed"; 18 "Removed". Many of these refer to the same actions done at different times & places (and I'm hoping that makes thing easier!)

    Happy to send a zipped folder to any bidder!


    Re: Determine 32- vs 64-bit & test compatibility

    In the interim, I finally confirmed mine is 32-bit Excel, but I'm not sure what an "API call" is (!). I do have a variety of things going on in this spreadsheet - actually, 5 linked workbooks. The most "exotic" things: (1) as the first workbook opens, it does a web query to check for updates at my website:

    Re: Determine 32- vs 64-bit & test compatibility

    Uhhhmmm... if I understand your question correctly, yes. In "declarations" (following Option Explicit) many/most of my macros list "Dim LastRow As Long" , and references to "LastRow" phrase appears repeatedly within the macro itself. (I've used this to copy formulae to as many as 60,000 rows)

    So... is this indicative of 32- or 64-but code, and what's the OTHER version? (And what else should I be looking for?)

    Thanks for your help!

    Hi, All

    I'm trying to determine whether (1) my Excel 2010 installation is 32 bit or 64 bit; (2) same question re installed Excel 2013 on the same computer; and (3) how to check existing code for compatibility with BOTH 32- and 64-bit versions.

    I'm running Windows 7, the spreadsheets I'm using were originally in Excel 2007, run fine in 2010, and run accurately (but v-e-r-y slowly) in 2013, with some odd display issues. Another user has reported code problems using Windows 8.1 and 64-bit Excel 2013. (But I am mystified by the guidance I found at the MS website...)

    Thanks in advance for any help/suggestions!


    Re: Combobox selection to go into cell value

    Hi, chrycy

    Right click on your combo box and select 'Format Control'... you'll get a pop-up showing your box has no "Cell Link." If - as shown - this is a combo box NOT within a User Form, you have a couple of options...

    -- If you enter $B$2 as the cell link, B2 will display a numeric value keyed to whether the user selected the first, second or "nth" option you've provided in your combo box, based on their relative locations within the input range. (Your sample shows 4 possible responses, listed at input range $E$3:$E$6 and hidden by text color)

    -- If you enter $C$3 as the cell link, C3 (hidden by the combo box itself) will display the numeric value but not be visible to the user. At B2, you could have the formula : =IF($C$3=1,"a",IF($C$3=2,"b",IF($C$3=3,"c","d"))) Voila! the text you want shows up at B2.

    -- If you choose a cell anywhere else on the page as the cell link, you can hide it by text color as you did with the input range, and write the formula at B2 as above, but the conditionals would refer to the cell you've chosen.

    The second or third options above would work if the combo box has only 4 options... but I see your combo box provides for 70 lines' worth of options....

    Other similar options are available to you if you choose to create a user form, which may be worth investigating!