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
    253

    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 offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    XlApp.visible = true
    Is correct and has always worked.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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........

  4. #4
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    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
    253
    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 offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help.....

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