Open workbook and make it active workbook

[Human_doing]

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

 

[Andrew Poulsom]

When you open a workbook it becomes the ActiveWorkbook.

 

[Human_doing]

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 xublishsource=", _
"align=left xublishsource=")

'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

 

[Andrew Poulsom]

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

MsgBox ActiveSheet.Parent.Name

does it return what you expect?

 

[Human_doing]

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?

 

[Andrew Poulsom]

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

 

[Human_doing]

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

Thanks