I have the following code which I have patched together
from some of my code and some I have ripped from past
entries on the Newsgroup but I have hit a brick wall.

I have a worksheet with two sheets. Menu and Table_import.

The macro should import the all the values of the workbook
fname which is generated from the
Application.GetOpenFilename method. It should then add all
sheet names to a list box (lst_sheetnames) I have on a
userform called frm_sheetnames.

The user then select which sheet they want to import in
using a cmd button called cmd_select.

I have two problems with this. for somereason it is not
adding the sheet names to the list box. Also how would I
carry on running the macro when the user clicks cmd_select.

Also when it comes up with the error subscript out of
range and doesnt copy the data from fname?

Any help would be appreciated.

Thanks

Edgar Thoemmes





Sub Import_Wizard()
Dim fname As String
Dim oWb As Workbook
Dim sh As Worksheet
Dim shName As String

'Get file path for import

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

'Check that a file was selected
If fname < "" Then
Set oWb = Workbooks.Open(fname)

'load form
frm_sheetnames.Show

'Copy sheet names to list (lst_sheetnames) on user form
(frm_sheetname)
With Workbooks(fname)
For i = 1 To .Worksheets.Count
frm_sheetname.lst_sheetnames.AddItem = .Worksheets(i).Name
Next i
End With

'Need to change Sheet name to to selected item on list???

oWb.Sheets("TEST11").Range("A1:AQ100").Copy
Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste

Else
MsgBox ("Please select a Valid File")

End If
oWb.Close

End Sub

Edgar,

To get the worksheet names in the list box, try replacing this line...
frm_sheetname.lst_sheetnames.AddItem = .Worksheets(i).Name
with...
frm_sheetname.lst_sheetnames.AddItem .Worksheets(i).Name

Regards,
Jim Cone
San Francisco, CA

"Edgar Thoemmes" wrote in message
...
I have the following code which I have patched together
from some of my code and some I have ripped from past
entries on the Newsgroup but I have hit a brick wall.
I have a worksheet with two sheets. Menu and Table_import.
The macro should import the all the values of the workbook
fname which is generated from the
Application.GetOpenFilename method. It should then add all
sheet names to a list box (lst_sheetnames) I have on a
userform called frm_sheetnames.
The user then select which sheet they want to import in
using a cmd button called cmd_select.
I have two problems with this. for somereason it is not
adding the sheet names to the list box. Also how would I
carry on running the macro when the user clicks cmd_select.
Also when it comes up with the error subscript out of
range and doesnt copy the data from fname?
Any help would be appreciated.
Thanks
Edgar Thoemmes

Sub Import_Wizard()
Dim fname As String
Dim oWb As Workbook
Dim sh As Worksheet
Dim shName As String
'Get file path for import
ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas Payments
Public\Ed"
fname = Application.GetOpenFilename()
'Check that a file was selected
If fname < "" Then
Set oWb = Workbooks.Open(fname)
'load form
frm_sheetnames.Show
'Copy sheet names to list (lst_sheetnames) on user form
(frm_sheetname)
With Workbooks(fname)
For i = 1 To .Worksheets.Count
frm_sheetname.lst_sheetnames.AddItem = .Worksheets(i).Name
Next i
End With
'Need to change Sheet name to to selected item on list???
oWb.Sheets("TEST11").Range("A1:AQ100").Copy
Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste
Else
MsgBox ("Please select a Valid File")
End If
oWb.Close
End Sub