how to load a file

[Joy]

I am new to Excel vba programming. I am stuck with a problem:
We want to put a button on an Excel sheet. when user clicks it, there will
be browse message box popping up, and user can select a file stored in local
disk. When it is successfully loaded, the macro could read data from the file.

Can anyone give some sketch of how to do it?

many many thanks!!

 

[Gord Dibben]

You could start with this which displays your default folder.

Sub open_file()
filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen <> False Then
Workbooks.Open Filename:=filetoopen
End If
End Sub

Browse to your workbook and Open it.

Note: the opened workbook will now be Activeworkbook.


Gord Dibben MS Excel MVP

 

[Rich Locus]

Joy:

Gord's solution is perfect. I'm just including this code because I do this
function so often that I created a callable subroutine that I use to open
files.

Option Explicit
Option Base 1

' ************************************************
' Variables For File Open Dialogue Box
' ************************************************
Public strDialogueFileTitle As String
Public strFilt As String
Public intFilterIndex As Integer
Public strCancel As String
Public strWorkbookFullPathAndName As String
Public strWorkbookOnlyName As String

Public Sub ExampleOfCallingOpenAFile()

' ****************************************************************************
' Present the Open File Dialogue To The User
' ****************************************************************************
strFilt = "Excel Files (*.xls),*.xls," & _
"CSV Files (*.csv),*.csv,"

intFilterIndex = 1

strDialogueFileTitle = "Select Your Input File of Choice"

Call OpenFileDialogue

If strCancel = "Y" Then
MsgBox ("An Open Error Occurred Importing Your File Selection")
Exit Sub
End If

' ****************************************************************************
' Confirmation
' ****************************************************************************
MsgBox ("You Opened Workbook " & strWorkbookOnlyName)
MsgBox ("Your Current Worksheet is " & ActiveSheet.Name)

End Sub

Sub OpenFileDialogue()

' ************************************************
' Display a File Open Dialogue Box For The User
' ************************************************
strCancel = "N"
strWorkbookFullPathAndName = Application.GetOpenFilename _
(FileFilter:=strFilt, _
FilterIndex:=intFilterIndex, _
Title:=strDialogueFileTitle)

' Exit If No Filename Selected
If strWorkbookFullPathAndName = "" Then
MsgBox ("No Filename Selected")
strCancel = "Y"
Exit Sub
ElseIf strWorkbookFullPathAndName = "False" Then
MsgBox ("You Clicked The Cancel Button")
strCancel = "Y"
Exit Sub
End If

' ************************************************
' Open The Workbook Selected
' ************************************************
Workbooks.Open strWorkbookFullPathAndName

' ************************************************
' Save the Workbook Name Without the Path
' ************************************************
strWorkbookOnlyName = ActiveWorkbook.Name

End Sub

 

[Gord Dibben]

I need al the help I can get Rich<g>

My VBA is "Very Basic Applications"


Gord