Results 1 to 5 of 5

Setting object variables

  1. #1
    Glenn_Suggs is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    70

    Setting object variables

    Hello Everyone... Can anyone tell me the syntax for setting an object variable (specifically RANGE in Excel) through VBA in MS Access. This is what I have and the error is that the Range object (_Global) failed.



    Code:
    Set xlsApp = New Excel.Application
    
    xlsApp.Workbooks.Open Filename:=[filename]
    
    xlsApp.Cells.Range("B9:N30).Select
    
    With xlsApp.Selection
       .HorizontalAlignment = xlCenter
    End With
    Thanks in Advance,
    Glenn

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,538
    Missing a quote mark in ("B9:N30")
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    The application object does not have a range.
    This is the go to site for this sort of thing for a lot of people, I dare say
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

    Follow the exporting or importing link and see what you think. KS pretty much declares a variable for EACH Excel object and uses them in a way I found easy to follow.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,538
    Good point Micron.

    Following works for me.

    Code:
    Set xlsApp = New Excel.Application
    Set xlsWB = xlsApp.Workbooks.Open Filename:=[filename]
    xlsWB.Worksheets("Sheet1").Range("B9:N30").HorizontalAlignment = xlCenter
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    Glenn_Suggs is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    70
    Quote Originally Posted by Micron View Post
    The application object does not have a range.
    This is the go to site for this sort of thing for a lot of people, I dare say
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

    Follow the exporting or importing link and see what you think. KS pretty much declares a variable for EACH Excel object and uses them in a way I found easy to follow.
    Thanks, Micron. Looks like some pretty good stuff on that website.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-18-2018, 02:29 PM
  2. Calling Form Object using Variables
    By J Bhujanga in forum Programming
    Replies: 4
    Last Post: 01-12-2017, 02:24 PM
  3. Using variables in another object
    By system243trd in forum Programming
    Replies: 7
    Last Post: 01-06-2016, 05:27 PM
  4. VBA Optimization - Forms in Object Variables
    By GeekInOhio in forum Programming
    Replies: 1
    Last Post: 09-18-2012, 01:28 PM
  5. Setting global variables
    By Remster in forum Programming
    Replies: 1
    Last Post: 08-24-2011, 07:47 AM

Posting Permissions

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