Open Excel File

[josros60]

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:

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.

 

[dmt32]

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

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

 

[josros60]

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:

Set Wb = Application.Workbooks.Open(File, False, False)

thansk again

 

[dmt32]

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:

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

Set Xl = GetObject(, "excel.application")

and update line giving the error

Set Wb = Xl.Workbooks.Open(File, False, False)

If still an issue hopefully someone with up to date Outook expertise can step in

Dave

 

[josros60]

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.

 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