Results 1 to 6 of 6
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    236

    Post Excel Won't stay visible

    I have the following code i am trying to get working. For some reason Excel won't stay open to be modified. Could someone please let me know where i am going wrong. Thank you in advance. --Walker



    Code:
    Private Sub Command6_Click()
          Dim filepath As String
        Dim xlApp As Excel.Application
        Dim wb As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        
        filepath = "C:\Users\ew\Desktop\modtest.xlsx"
        
            Set xlApp = CreateObject("Excel.Application")
            Set wb = GetObject(filepath)
            
            xlApp.Visible = True
            Set xlSheet = wb.Worksheets(1)
            xlSheet.Visible = True
            
    '
            xlSheet.Range("c4") = "test"
                With Sheets("sheet1").Range("c4")
                     With .Font
                         .Size = 24
                         .Bold = True
                     End With
                End With
    '
            Set xlApp = Nothing
            Set wb = Nothing
            Set xlSheet = Nothing
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,188
    XlApp.visible = true
    Is correct and has always worked.

  3. #3
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,713
    I tried your code, Excel would be visible, but Sheet1 would never appear.

    I copied some code from Ken Snell's site (http://www.accessmvp.com/KDSnell/EXC...m#WriteRstFile) and with a little modification, it seemes to work.

    I changed your variable names - maybe you can change them back to your variable names.
    Code:
    Private Sub Command6_Click()
        Dim filepath As String
        Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    
        filepath = "C:\Users\ew\Desktop\modtest.xlsx"
       
         ' Replace True with False if you do not want the first row of
        ' the worksheet to be a header row (the names of the fields
        ' from the recordset)
        '    blnHeaderRow = True
    
        ' Establish an EXCEL application object
        On Error Resume Next
        Set xlx = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
            Set xlx = CreateObject("Excel.Application")
        End If
        Err.Clear
        On Error GoTo 0
    
        ' Change True to False if you do not want the workbook to be
        ' visible when the code is running
        xlx.Visible = True
    
        ' Replace C:\Filename.xls with the actual path and filename
        ' of the EXCEL file into which you will write the data
        Set xlw = xlx.Workbooks.Open(filepath)
    
        ' Replace WorksheetName with the actual name of the worksheet
        ' in the EXCEL file
        ' (note that the worksheet must already be in the EXCEL file)
        Set xls = xlw.Worksheets(1)
    
        '
        xls.Range("c4") = "test"
        With Sheets("sheet1").Range("c4")
            With .Font
                .Size = 24
                .Bold = True
            End With
        End With
        '
        xlw.Close   ' <<-- I added this
        Set xls = Nothing
        Set xlw = Nothing
        Set xlx = Nothing
    
    End Sub
    Good luck with your project........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    236
    Steve,
    I replaced my code with yours and it works great. I have one one other question about this. I now have 2 subroutines and they both need the filepath variable. I am having a brain fart and dont remember how to use one variable for both subroutines. Thank you for your help.
    --Walker

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    236
    Steve,

    Never mind the last question. I remembered that i only had to take the variable out of the subroutines and put it at the top. Thank you for your help.

  6. #6
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,713
    Happy to help.....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 3
    Last Post: 05-02-2018, 11:37 AM
  2. Replies: 5
    Last Post: 08-14-2017, 02:19 AM
  3. Replies: 16
    Last Post: 03-26-2015, 08:35 PM
  4. Export Queries to Excel with Combo-Box visible in Excel
    By johnmarc2 in forum Import/Export Data
    Replies: 1
    Last Post: 07-07-2014, 05:33 PM
  5. Replies: 2
    Last Post: 01-06-2011, 04:38 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 - Senior Forums