Results 1 to 5 of 5
  1. #1
    Glenn_Suggs is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    72

    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
    52,825
    Missing a quote mark in ("B9:N30")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Glenn_Suggs is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    72
    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, 03:29 PM
  2. Calling Form Object using Variables
    By J Bhujanga in forum Programming
    Replies: 4
    Last Post: 01-12-2017, 03:24 PM
  3. Using variables in another object
    By system243trd in forum Programming
    Replies: 7
    Last Post: 01-06-2016, 06:27 PM
  4. VBA Optimization - Forms in Object Variables
    By GeekInOhio in forum Programming
    Replies: 1
    Last Post: 09-18-2012, 02:28 PM
  5. Setting global variables
    By Remster in forum Programming
    Replies: 1
    Last Post: 08-24-2011, 08: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
  •  
Other Forums: Microsoft Office Forums