Import Excel file into Excel

[blabibla]

Hi,
i'm new to VBA and i really need some help with this. I have a User Form with a button which should open excel file(which the user must select via Application.FileDialog(msoFileDialogOpen) into excel) and i have absolutely no idea how to do that. I'll be very thankful for any help

 

[Bong]

fnam = Application.GetOpenFilename()
Workbooks.Open (fnam)

_________________
Bob Rashkin

 

[SkipVought]

You can use the

Application.GetOpenFileName Method

and

MS Query to import a sheet from your workbook.

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blabibla]

i thought that Bong's code was perfect, but then i noticed that it opens it as another document, but i need it to be in the current one starting in the selected cell and since i don't know how big will be desired document or if it'll have a table in it i can't use SkipVought's reply too...

 

[SkipVought]

Huh?

My solution is exactly what you need to IMPORT rather than OPEN.

What is the apparent problem?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blabibla]

i'm sorry, i probably didn't understand it well (my english is not good), i'll go read it again

 

[blabibla]

OK i read it several times and i don't know how to combine Application.GetOpenFileName Method with it... i don't know how it should look like in VBA at all...

 

[SkipVought]

You want the user to be able to specify the workbook. THAT is where you use the GetOpenFileName method; ONLY to get the file name and path.

THAT file name and path will then have to be used to MODIFY the Connection property of the QueryTable object, before the query is refreshed, in order to IMPORT the data from THAT workbook.

I'd suggest that you FIRST go on your sheet and get the data from one of your workbooks. Then turn on your macro recorder and EDIT the query. This will get you the basic code you will need to customize, in order to do what you described.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blabibla]

it turned out that on the computer that it'll be used there is no MS Query installed and i'm supposed not to use it, so if you know another way i'll be very thankful, if not - i have no idea what i'm going to do...

 

[SkipVought]

You can also query using ActiveX Data Objects, but it will be more difficult to do.

What version of Excel on the machine you will be using?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blabibla]

2003

 

[SkipVought]

What happens if you do...

Data > Import External Data > New Database Query - Excel files... ?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blabibla]

when i get to New Database Query it says "this future is not currently installed".

 

[SkipVought]

OK.

Here'a an example of an Excel query using ADO...

Sub AppendData()
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    Dim sh As Range, lRow As Long, wsData As Worksheet
 '[b]the workbook I am opening is in the same folder as the ThisWorkbook [/b]
    sPath = ThisWorkbook.Path        
    sDB = "Backup July 2006 (Week 5)"
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    For Each sh In [SheetName]
'[b]this SQL code will be specific to your requirements[/b]
        sSQL = "SELECT A.PN"
        sSQL = sSQL & ", A.RQDATE"
        sSQL = sSQL & ", A.QTY"
        sSQL = sSQL & ", A.COST"
        sSQL = sSQL & ", A.NOMEN"
        sSQL = sSQL & ", A.`GROUP`"
        sSQL = sSQL & ", A.`Late Pieces`"
        sSQL = sSQL & ", A.BackLog "
'[b]the sheet name followed by a DOLLAR SIGN[/b]
        sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`" & sh.Value & "$` A "
        
        sSQL = sSQL & "WHERE (A.`Late Pieces`>0 OR A.BackLog>0) "
        sSQL = sSQL & "  AND (A.COE='DSC') "
        
        [Sql] = sSQL
        
        With rst
           .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
           
            With wsData
               lRow = .UsedRange.Rows.Count + 1
               .Cells(lRow, 1).CopyFromRecordset rst
               .Range(.Cells(lRow, .UsedRange.Columns.Count), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Value = sh.Value
            End With
        
           .Close
        End With
    Next
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

You must have a reference set to Microsoft ActiveX Data Objects m.n Library

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blabibla]

it gives me a mistake here

 Dim rst As ADODB.Recordset

the mistake is "user-defined type is not defined

 

[SkipVought]

You did not set a reference to the object library in Tools > References in the VB Editor, as instructed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blabibla]

sorry for how lame i am, but if you mean to check ActiveX Data Objects then i don't have it in tools>references and if i try to check "active directory types" for instance it gives me this error "Error in loading DLL" and it gives me this mistake if i try to check anything starting with "active...".

 

[SkipVought]

As posted in 24 Jun 10 13:12

Microsoft ActiveX Data Objects m.n Library



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blabibla]

run-time error '424':
object required in:

For Each sh In [SheetName]

 

[SkipVought]

I have a Named Range named SheetName, that contains a list of sheet names.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}