Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling a named range in Excel with Access data (2000)

    How can I fill the column range named "GRTN" on the "Bilaterali" worksheet in the attached xls with data coming from the "kW" column on the "QAMdati" report in the attached mdb?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling a named range in Excel with Access data (2000)

    Select cells E3:E26 and clear them.
    Select Data | Get External Data | New Database Query...
    The Query Wizard starts.
    Select an Access data source or create one.
    Point to the database.
    Expand the QAMdatix query.
    Select the Expr1 ( <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>) field, and click the > button.
    Click Next, Next, Next, Finish.
    In the Import Data dialog, click Properties...
    Clear the "Include field names" check box.
    Click OK twice.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a named range in Excel with Access data (2000)

    Thank you Hans,
    Why does the import procedure insert a new column instead of filling the column range named "GRTN"?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling a named range in Excel with Access data (2000)

    If you select "Overwrite existing cells..." in the Properties window (where you uncheck the "Include field names" box, the data will be placed in the existing column instead of inserting a new column.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a named range in Excel with Access data (2000)

    Perfect! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>.
    I've prepared a macro(macro1 in module2 of the attached xls) that does all that stuff.
    How can you launch that macro from within the attached mdb, provided both the mdb and the xls files are in the same folder?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling a named range in Excel with Access data (2000)

    You can uase Automation to open the workbook from Access and run the macro. You already have a reference to the Excel object library in your database, so you can run Excel code. Here is a function that should go into a standard module in the database. It can be called from a macro, or from the On Click event of a command button of a form. It compiles without problems, and it starts the Excel macro, but the Excel code won't run on my PC (too many specific Italian elements), so I haven't tested it completely.

    Function ImportExcel()
    Dim objXl As Excel.Application
    Dim objWb As Excel.Workbook
    Dim fStart As Boolean
    Dim strPath As String

    On Error Resume Next

    Set objXl = GetObject(, "Excel.Application")
    If objXl Is Nothing Then
    Set objXl = CreateObject("Excel.Application")
    If objXl Is Nothing Then
    MsgBox "Cannot activate Excel.", vbCritical
    Exit Function
    End If
    fStart = True
    End If

    On Error GoTo ErrHandler

    strPath = CurrentProject.Path
    Set objWb = objXl.Workbooks.Open(strPath & "Crea_File_x_GRTN.xls")
    objWb.Worksheets("Bilaterali").Activate
    objXl.Run "Macro1"
    objWb.Save

    ExitHandler:
    On Error Resume Next
    objWb.Close SaveChanges:=False
    Set objWb = Nothing
    If fStart Then
    objXl.Quit
    End If
    Set objXl = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a named range in Excel with Access data (2000)

    I've noticed the macro doesn't run if the mdb file is open. Any workaround?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling a named range in Excel with Access data (2000)

    If you have just modified something in the design of the database (for example the code to run the macro <img src=/S/grin.gif border=0 alt=grin width=15 height=15>), you have locked the database for exclusive use, so Excel cannot open the database. After modifying the design, you must close the database and reopen it, then run the code.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a named range in Excel with Access data (2000)

    I mean I tried the macro in Excel even before creating the Function ImportExcel() and if the mdb is open it doesn't work.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling a named range in Excel with Access data (2000)

    Sub Macro1()
    Dim wsh As Worksheet
    Set wsh = Worksheets("Bilaterali")
    With wsh.QueryTables.Add( _
    Connection:="ODBC;DSN=Database di Microsoft Access;" & _
    "DBQ=Cocuments and Settingsu01074673DocumentiGRTNgrtn.mdb", _
    Destination:=wsh.Range("D3"), _
    Sql:="SELECT Expr1 FROM GRTN")
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = True
    .SaveData = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    With wsh.QueryTables.Add( _
    Connection:="ODBC;DSN=Database di Microsoft Access;" & _
    "DBQ=Cocuments and Settingsu01074673DocumentiGRTNgrtn.mdb", _
    Destination:=wsh.Range("A3"), _
    Sql:="SELECT giorno FROM GRTN")
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = True
    .SaveData = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    wsh.Range(wsh.Range("A3"), wsh.Range("A65536").End(xlUp)).NumberFormat = "m/d/yyyy"
    End Sub

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a named range in Excel with Access data (2000)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    The dates in the A column(which is formatted as general, not date, in the original Excel file posted at the beginning of the thread) of the Excel file should display as 20040701(which corresponds to July 1, 2004, last available date in the PREVISIONI table) so I've tried changing the GRTN query into:

    SELECT Format(Year([Giorno]),"yyyy") & Format(Month([giorno]),"mm") & Format(Day([giorno]),"dd") AS Expr2, [Ora]+1 AS Espr1, (([termica]+[brembana]+[scalve]+[seriana]+[palazzolo])-([rezzato]+[calusco]+[sede])-[Prev])/1000 AS Expr1
    FROM Previsioni
    WHERE giorno=(select max(b2.giorno) from previsioni as B2)
    ORDER BY Format(Year([Giorno]),"yyyy") & Format(Month([giorno]),"mm") & Format(Day([giorno]),"dd"), [Ora]+1;

    but I get 19050131 as a result. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling a named range in Excel with Access data (2000)

    This is because you're doing too much: you both take Year (and Month and Date) AND apply a Format function. You must do either one or the other, not both. The easiest one is Format([Giorno],"yyyymmdd"). The complete SQL becomes:

    SELECT Format([Giorno],"yyyymmdd") AS Expr2, [Ora]+1 AS Espr1, (([termica]+[brembana]+[scalve]+[seriana]+[palazzolo])-([rezzato]+[calusco]+[sede])-[Prev])/1000 AS Expr1
    FROM Previsioni
    WHERE (((Previsioni.Giorno)=(select max(b2.giorno) from previsioni as B2)))
    ORDER BY Format([Giorno],"yyyymmdd"), [Ora]+1;

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a named range in Excel with Access data (2000)

    Happy new year Hans,
    the function doesn't run if the Excel file is already open.
    How can I change
    Set objWb = objXl.Workbooks.Open(strPath & "Crea_File_x_GRTN.xls")
    to make up for that?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling a named range in Excel with Access data (2000)

    Felice anno nuovo!

    Try this:

    ' Suppress error messages
    On Error Resume Next
    ' Try to get opened workbook
    Set objWb = objXl.Workbooks("Crea_File_x_GRTN.xls")
    ' Restore error handling
    On Error GoTo ErrHandler
    ' If workbook was not open, objWb is Nothing ...
    If objWb Is Nothing Then
    ' ... so open it
    Set objWb = objXl.Workbooks.Open(strPath & "Crea_File_x_GRTN.xls")
    End If

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a named range in Excel with Access data (2000)

    Thank you so much Hans,
    How come if the Excel file is already open, it gets closed after the function has run? I don't see any line of code that could cause that

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •