Results 1 to 9 of 9
  1. #1
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39

    Module with Excel VBA Constants


    Of late, I have been doing a lot of work in MS-Access manipulating Excel Spreadsheet.

    I think I have mastered doing this using late binding. The only inconvenience seems to be that I have to look up each Excel VBA constant and define it in the App. Has anyone created a module that has all of the Excel constant codes, such as the following:

    Code:
    Option Compare Database
    Option Explicit
    
    Public Const xlPasteFormats = -4122
    Public Const xlSortOnValues = 0
    Public Const xlAscending = 1
    Public Const xlSortNormal = 0
    Public Const xlYes = 0
    Public Const xlTopToBottom = 1
    etc...

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Suggest you include a reference to Microsoft Excel 16 Object library.
    Click image for larger version. 

Name:	excelConstants.PNG 
Views:	16 
Size:	48.7 KB 
ID:	43235

  3. #3
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    Orange, using the Excel 16.0 Object Library reference is what I am trying to avoid by using late binding. I use it in development, but at least one developer on this site changes his code to late binding prior to release to avoid the problem of having to rebind if a different version of Excel and Access is used by a client.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes, I understand your intent/wish to use late binding. But, as per Daniel Pineault's materials, it seems you lose the ability to use constants with late binding. I'm not aware of options, but others more familiar with Excel( and/or interaction of Office products) may have some technique(s).
    Last edited by orange; 10-16-2020 at 11:09 AM. Reason: spelling

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi again
    Attached is a module I acquired many years ago for exactly that purpose.
    It may not have some of the latest commands as a result.
    All credit should be to the author Mike Alexander as noted and not me

    Its a zipped .bas file so you can just import it into the VBE

    P.S. Thanks for the PM which I just realised I forgot to reply to ....
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks Colin. I had never seen that and I do miss the DataPig stuff that Mike Alexander provided for years.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ah yes. That's who the author is! I expect I got that from the DataPig site at some point
    I agree totally. A huge shame that the DataPig videos are no longer available
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    Thanks, isladogs. Just what I was looking for.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome. Hope it helps
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting a module into an Excel file
    By Sephaerius in forum Import/Export Data
    Replies: 3
    Last Post: 12-18-2018, 08:29 AM
  2. Using Summed vb Constants
    By dunc723 in forum Programming
    Replies: 3
    Last Post: 05-07-2018, 07:36 PM
  3. create excel module/macro with access vba
    By trevor40 in forum Programming
    Replies: 3
    Last Post: 02-01-2015, 08:55 PM
  4. Replies: 4
    Last Post: 03-13-2013, 08:57 PM
  5. Converting Excel Macro into Access Module
    By diddyville in forum Modules
    Replies: 1
    Last Post: 03-28-2011, 07:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums