Results 1 to 5 of 5

Thread: Open Excel File
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    397
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Open Excel File

    Hi,

    How can this code be modify just to open the file and not open a new workbook too.

    When I click in the macro open the file in the path but also open a blank sheet.

    here is the code:

    Code:
    Public Sub OpenVendorMappingAccountsTracking()  Dim File$
      Dim Xl As Object ' Excel.Application
      Dim Wb As Object ' Excel.Workbook
      Dim Ws As Object ' Excel.Worksheet
      Dim Rn As Object ' Excel.Range
    
    
      File = "C:\Users\user\Desktop\Excel Files\BILL PAYMENTS LIST.xlsm"
    
    
      On Error Resume Next
      Set Xl = GetObject(, "excel.application")
      On Error GoTo 0
      If Xl Is Nothing Then Set Xl = New Excel.Application
      Set Wb = Xl.Workbooks.Open(File)
      Set Ws = Wb.Sheets(1)
      Ws.Activate
      Set Rn = Ws.Range("a1")
      Rn.Activate
      Xl.Visible = True
    End Sub
    thanks.

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,856
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Open Excel File

    Hi,
    untested but try this update to your code & see if does what you want

    Code:
    Public Sub OpenVendorMappingAccountsTracking()
      Dim File$
      
      Dim Wb As Workbook ' Excel.Workbook
      Dim Ws As Worksheet ' Excel.Worksheet
      Dim Rn As Range ' Excel.Range
    
    
    
    
      File = "C:\Users\user\Desktop\Excel Files\BILL PAYMENTS LIST.xlsm"
    
    
      If Dir(File, vbDirectory) <> vbNullString Then
        Set Wb = Application.Workbooks.Open(File, False, False)
        Set Ws = Wb.Sheets(1)
        Ws.Activate
        Set Rn = Ws.Range("a1")
        Rn.Activate
      Else
        MsgBox File & Chr(10) & Space(Len(File) / 2) & "File Not Found!", 48, "Not Found"
      End If
    End Sub
    Dave
    Last edited by dmt32; Jun 18th, 2019 at 02:17 PM.

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    397
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Open Excel File

    Hi Dave,

    thank you for your help.

    run the code and gave me run-time error 438 object doesn't support this property or method ( Just to let you know doing this from Outlook) this is the line it highlights:

    Code:
    Set Wb = Application.Workbooks.Open(File, False, False)
    thansk again

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,856
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Open Excel File

    Quote Originally Posted by josros60 View Post
    Hi Dave,

    thank you for your help.

    run the code and gave me run-time error 438 object doesn't support this property or method ( Just to let you know doing this from Outlook) this is the line it highlights:

    Code:
    Set Wb = Application.Workbooks.Open(File, False, False)
    thansk again
    Information like that always helpful as I assumed you were working from within Excel Application.

    I am very rusty working from other MS applications to Excel

    you probably need to restore this line

    Code:
    Set Xl = GetObject(, "excel.application")
    and update line giving the error

    Code:
    Set Wb = Xl.Workbooks.Open(File, False, False)
    If still an issue hopefully someone with up to date Outook expertise can step in

    Dave

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Posts
    397
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Open Excel File

    thank you for your help.

    was still giving me error but found the solution in the web, leaving the code if it can help somebody.

    Code:
     Public Sub OpenSpecificExcelWorkbook()
    
        Dim xExcelFile As String
        Dim xExcelApp As Excel.Application
        Dim xWb As Excel.Workbook
        Dim xWs As Excel.Worksheet
        Dim xExcelRange As Excel.Range
        xExcelFile = "C:\Users\user\Desktop\Excel Files\FILENAME.XLS"
        Set xExcelApp = CreateObject("Excel.Application")
        Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
        Set xWs = xWb.Sheets(1)
        xWs.Activate
        Set xExcelRange = xWs.Range("A1")
        xExcelRange.Activate
        xExcelApp.Visible = True
    
    
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •