PDA

View Full Version : import data



viva_
12-30-2007, 06:03 AM
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
12-30-2007, 08:52 AM
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.

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

viva_
12-30-2007, 09:17 AM
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
12-30-2007, 10:09 AM
Not sure but maybe this is what you are looking for:

Sub a()
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = -1 Then
MsgBox .SelectedItems(1)
End If
End With
End Sub

viva_
01-02-2008, 08:45 AM
Thanks!!!