Results 1 to 7 of 7

Open workbook and make it active workbook

This is a discussion on Open workbook and make it active workbook within the Excel Questions forums, part of the Question Forums category; Hi all, What I'm looking to do is have a button within an Excel workbook that opens up the 'open ...

  1. #1
    Board Regular
    Join Date
    Feb 2011
    Posts
    135

    Default Open workbook and make it active workbook

    Hi all,

    What I'm looking to do is have a button within an Excel workbook that opens up the 'open file' dialog. A file can then be selected and the new file is selected as the activeworkbook. The below code brings up the 'open file' dialog but doesn't seem to make it the active workbook.

    Any help much appreciated,

    Thanks

    NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
    If NewFN = False Then
    ' They pressed Cancel
    MsgBox "Stopping because you did not select a file"
    Else
    Workbooks.Open Filename:=NewFN
    End If

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,642

    Default Re: Open workbook and make it active workbook

    When you open a workbook it becomes the ActiveWorkbook.
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Feb 2011
    Posts
    135

    Default Re: Open workbook and make it active workbook

    Hi thanks for answering so quickly. Yes that's what I thought as well, maybe I should elaborate. What I'm trying to do is use Ron DeBruin's code to send active workbook as HTML in an email, combined with a facility for people to open up any workbook they choose and have sheet1 of that workbook copied and pasted in to the email. The below code is what I have, it opens up the other workbook but then seems to paste the contents of the initial workbook in to the email:

    Sub HRADSEMAIL()

    NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
    If NewFN = False Then
    ' They pressed Cancel
    MsgBox "Stopping because you did not select a file"
    Else
    Workbooks.Open Filename:=NewFN
    End If


    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .To = "ron@debruin.nl"
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .HTMLBody = RangetoHTML(rng)
    .Display 'or use .Display
    End With
    On Error GoTo 0

    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
    Function RangetoHTML(rng As Range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=TempFile, _
    Sheet:=TempWB.Sheets(1).Name, _
    Source:=TempWB.Sheets(1).UsedRange.Address, _
    HtmlType:=xlHtmlStatic)
    .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
    "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    End Function

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,642

    Default Re: Open workbook and make it active workbook

    If you add this line to your code (after the worbook is opened):

    Code:
    MsgBox ActiveSheet.Parent.Name
    does it return what you expect?
    Microsoft MVP - Excel

  5. #5
    Board Regular
    Join Date
    Feb 2011
    Posts
    135

    Default Re: Open workbook and make it active workbook

    Hi, it returns the name of the workbook that houses the VBA code, not the newly opened workbook. Any idea how to then switch to the other workbook, it will be a different file name each time?

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,642

    Default Re: Open workbook and make it active workbook

    I can't reproduce that behaviour (Excel 2003). Where is that code in your workbook?
    Microsoft MVP - Excel

  7. #7
    Board Regular
    Join Date
    Feb 2011
    Posts
    135

    Default Re: Open workbook and make it active workbook

    Thanks for your help I got it sorted in the end, your advice definitely put me in the right direction,

    Thanks

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
  •  


DMCA.com