If Problem

[Edgar]

Hi

I use the following code to open a workbook.

Set oWb = Workbooks.Open(fname)

Where fname is a variable from the following code

ChDrive "s"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas Payments
Public\Remittance"
fname = Application.GetOpenFilename()

I have tried to add a if statement to the first part of
the code so that if the user does not select a file that
they will just load the sheet("Menu") instead of getting
the error message.

If oWb <> "" Then
Set oWb = Workbooks.Open(fname)
Else
Worksheets("Menu").Select
End If

But this is coming up with the error that fname variable
has not been set.

Can anyone help?

TIA

 

[Frank Kabel]

Hi
not quite sure about the order of your code but try
sub foo()
Dim fname
ChDrive "S:"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas Payments
Public\Remittance"
fname = Application.GetOpenFilename()
if fname = false then
fname = "Menu"
end if
Set oWb = Workbooks.Open(fname)

 

[Vasant Nanavati]

Try:

If fname <> False Then ...

instead of:

If oWb <> "" Then ...

You are trying to evaluate oWb before it exists. Also, you are comparing a
Workbook object to a String.

 

[Edgar]

Hi

Thanks for you answers.

I have amended the code as per below but I am getting a a
error box saying false.xls could not be found?

Any ideas

All Code posted below

Dim fname As String
Dim oWb As Workbook
Dim osh As String
Dim CurrentSheet As Worksheet
Dim PrintDlg As DialogSheet
Dim sheetcount As Integer
Dim TopPos As Integer
Dim i As Integer
Dim cb As OptionButton
Sub Import_Wizard()

'Run procedures
Get_Name
Select_Sheets
Import_Data

End Sub

Sub Get_Name()

'Get file path for import
ChDrive "s"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas Payments
Public\Remittance"
fname = Application.GetOpenFilename()

End Sub

Sub Select_Sheets()

Application.ScreenUpdating = False

If fname <> False Then
Set oWb = Workbooks.Open(fname)
Else
End
End If

'Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
sheetcount = 0

'Add the Optionbuttons
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)

'Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
sheetcount = sheetcount + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(sheetcount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13

End If
Next i

'Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

'Set dialog height, width, and caption
With PrintDlg.DialogFrame
..Height = Application.Max(68, PrintDlg.DialogFrame.Top +
TopPos - 34)
..Width = 230
..Text = "Please Select Only One Sheet and Click Select:"
..Caption = "Select Sheet to Import"
End With

'Change tab order of OK and Cancel buttons
'so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

'Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If sheetcount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
osh = cb.Caption
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

'Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

'Reactivate original sheet
CurrentSheet.Activate

End Sub

 

[Vasant Nanavati]

It would help if you told us *where* you are getting the error message <g>.

 

[Edgar]

Hi Vasant

I am getting the error on the following lines.

If fname <> False Then
Set oWb = Workbooks.Open(fname)
Else
End
End If

Sorry but it seems it is a type mismatch error.

Thanks

 

[Frank Kabel]

Hi
try changing
DIM fname as string

to
Dim fname

 

[Tom Ogilvy]

Change

If fname <> False Then
Set oWb = Workbooks.Open(fname)
Else


to

If fname <> "False" Then
Set oWb = Workbooks.Open(fname)
Else

 

[Vasant Nanavati]

Good catch, Frank. Missed that.

--

Vasant

Hi
try changing
DIM fname as string

to
Dim fname