Open Excel File

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
778
Office Version
  1. 365
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top