import data

[viva_]

Hi,

I am trying to import an excel worksheet from a workbook into a different workbook. I get the file path with "GetOpenFileName" but i can't build a connection.

Is there anyone knows the solution?

[lucas]

Here is one that I use daily. It does a few more things than you mentioned but it is well commented and if you have questions you can post them here.

[VBA]Option Explicit
Sub ImportSalesSheet()
Dim Wkb As Workbook, BookKeep As Workbook
Dim strPath As String
Dim FileName As String
Dim strFullName As String
Dim IsOpen As Boolean
Dim i As Long
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Set sheetname to be copied
Const SheetToCopy = "Sales"
'look for the Invoice workbook in this documents path
strPath = ThisWorkbook.path
'comment line above and uncomment line below to use hard coded path for invoice.xls
'strPath = "C:\Documents\Test\"
'name of the file you wish to copy the Sales sheet from, change as needed
FileName = "Invoice.xls"

Set BookKeep = ActiveWorkbook
'Locate and delete the Sales Sheet from this workbook
On Error GoTo NotFound
i = Sheets(SheetToCopy).Index
Sheets(i).Delete
NotFound:
'Set value for i if sheet previously deleted
If i = 0 Then i = 1
strFullName = strPath & "\" & FileName
If IsWbOpen(FileName) Then
Set Wkb = Workbooks(FileName)
IsOpen = True
Else
Set Wkb = Workbooks.Open(strFullName)
IsOpen = False
End If
'Following line adds Sales sheet as the last sheet in the workbook
'Wkb.Sheets("Sales").Copy After:=BookKeep.Sheets(ThisWorkbook.Sheets.Count)
'comment line above and uncomment line below to copy sheet to same location
Wkb.Sheets(SheetToCopy).Copy Before:=BookKeep.Sheets(i)
Application.CommandBars("Nav Tool").Delete
If Not IsOpen Then Wkb.Close False
Application.ScreenUpdating = True
MsgBox "Latest version of Sales Sheet successfully copied to this workbook.", vbInformation
Application.EnableEvents = True
Application.DisplayAlerts = True
Set BookKeep = Nothing
Set Wkb = Nothing
End Sub
'Zack contributed functions to check if workbook and sheet is open and/or exists
Function IsWbOpen(wbName As String) As Boolean
On Error Resume Next
IsWbOpen = Len(Workbooks(wbName).Name)
End Function[/VBA]

[viva_]

Thanks a lot for the answer but i guess i have to be more precise with my problem.
Assume that there is a folder named Data on desktop and every month a new workbook is added in the Data folder. These workbooks are named as Jan-Revenue, Feb-Revenue, Mar-Revenue etc... Each workbook has a single worksheet that contains related data. I am writing an amateur program for a company. And i want to user be able to add these monthly revenue sheets to the program( So the analyses coded will take place automatically)
i get the file path with the following code
userfile=Application.GetOpenFilename("Excel Files (*.xls), *.xls", , _
"Select a file to import", , False)

but i can't import the sheet to the current workbook

Thanks

[lucas]

Not sure but maybe this is what you are looking for:
[VBA]
Sub a()
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = -1 Then
MsgBox .SelectedItems(1)
End If
End With
End Sub
[/VBA]

[viva_]

Thanks!!!