Open Excel File

josros60

Active Member
Joined
Jun 27, 2010
Messages
498
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,838
Office Version
2019
Platform
Windows
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:

josros60

Active Member
Joined
Jun 27, 2010
Messages
498
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,838
Office Version
2019
Platform
Windows
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
 

josros60

Active Member
Joined
Jun 27, 2010
Messages
498
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,603
Messages
5,469,638
Members
406,661
Latest member
west5405

This Week's Hot Topics

Top