Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Import Excel Range into a table

    Hi, I have been asked to look at this issue and I am unsure if it can be done, if it can be done then how is it possible for it to be done...

    I am wanting to import data from 5 different sheets in an excel spreadsheet. All the data is on the same layout on each worksheet from a range of A21:BF42, A76:BF97 & A131:BE152.
    I need to import this information on a weekly basis, to an import table in a database and then I can query the information from there. My final problem is that the excel spreadsheet that I want to extract the data from is sent from an outside source and has been password protected.

    How can this be done?

    Jez

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Try with MAKE TABLE QUERY, or APPEND QUERY.

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MStef-ZG, Thanks for your reply but I dont understand what you mean...

    My data is currently in excel and I want to understand how its possible to whats in the above thread and import it into a database.

    Jez

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why not just import all the spreadsheet data and then use a query within Access to only view the selection you want?
    Depending on the size of the data of course.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Jez!

    Look at "DemoVidi1A2000.zip" (attachment).
    Here you have got EXCEL "Vidi_1" and Access mdb "DemoVidi1a2000.mdb".
    In MDB look at table. There are a link on EXCEL table, you MUST TO DO A
    RELINK THIS EXCEL TABLE, (depending on your Path), and you have got an
    access table "tblVidiAcc" (empty table).
    You have got a "Query1MakeTable". Run this query, and see in "tblVidiAcc".
    Adapt it on your mdb.
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MStef-ZG, Thanks for that example. I am still concerned about the spreadsheet being password protected and that is there a way around linking or importing data to a database table even though the spreadsheet is protected, also using code like this "DoCmd.TransferSpreadsheet acImport," only inputs the first worksheet, how can I get around this and specify which worksheet I want to import as I have 5 sheets to import from about 20 alltogether.

    Thanks,
    Jez

  7. #7
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Jez!

    Look at "DemoVidi1A2000" a new one.
    Open Form1 and try. Not forget to RELINK EXCEL spreadshit, (4 excel link).
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MStef-ZG, Thanks for the examples but its not really what I am looking for...

    I have rethought the whole thing and have now got all the data into ranges on an excel spreadsheet. What I want to do now is using a form in my database, bring in each range and import it into a individual tables.

    I have used this code below before to link a spreadsheet to a table which works fine but in this case I want only to import ranges of data as there are a number on the spreadsheet that all need importing to individual tables.

    This is my code that have used in the past...

    Private Sub cmdLoadData_Click()

    strPath = "\\titan\home\jeremyl1\"
    strFileName = Dir(strPath & "5HG_Feb_Comm.xls")
    strTempTable = "tbl18WeeksData"

    MsgBox "Data Loading...", vbInformation, "Jez"
    'If Len(strFileName) <> 0 Then
    DoCmd.TransferSpreadsheet acLink, , strTempTable, strPath & strFileName, True
    End If
    MsgBox "Import Complete", vbInformation, "Jez"
    End Sub


    How is this possible?
    Jez

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How much data is in your spreadsheet?
    Quote Originally Posted by georgev
    Why not just import all the spreadsheet data and then use a query within Access to only view the selection you want?
    Depending on the size of the data of course.
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have 5 ranges of data (all named) and they are 56Cols X 20Rows.

    all 5 range need to be imported to the relevant table (5 tables)

    Jez

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I asked for how much data was within your spreadsheet, if it's only 20 or so rows then why not just import the whole spreadsheet into a table and then query the "ranges" you need? Do you get what I mean?
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I understand what you mean by import the whole worksheet but dont understand how to pick ranges of data once its in a table.

    The ranges I want are all pretty much the same, they have same column headers and row headers, the only difference is the values under the column headers.

    would this make any difference?

    Jez

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can use a query to pick out the selection you want (assuming you can pick them out based on certain criteria (eg - Birth Date < '01/01/1990')

    How would you identify the ranges using logic (other than, "thee 20 rows next ot eachother") - remember that computers are stupid - you have to tell them exactly what you want!
    George
    Home | Blog

  14. #14
    Join Date
    Sep 2011
    Posts
    2
    What I've used so far to import a certain range is:

    Private Sub Button_Import_Excel_Click()
    On Error GoTo Err_Button_Import_Excel_Click

    'MsgBox (Application.SysCmd(acSysCmdAccessVer))

    'If Application.SysCmd(acSysCmdAccessVer) = "12.0" Then
    'MsgBox "Please start Access 2003 first before using this application.", vbInformation, "Wrong Version"
    'DoCmd.Quit
    'End If

    ' start
    'Turn system messages off
    ' DoCmd.SetWarnings False

    ' DoCmd.OpenQuery "Delete all entries"
    ' DoCmd.OpenQuery "Populate with new entries"

    'Turn system messages back on
    ' DoCmd.SetWarnings True

    ' end
    Dim Filename, Msg, Title
    Dim stDocName As String
    Dim Directory As String
    Dim Source_Unit As String
    Dim Storage_location As String
    Dim EmptySearch As Boolean

    Set oXL = CreateObject("Excel.Application")



    EmptySearch = True

    Title = "Importing Table"
    Msg = "Enter name for Supplier"
    Filename = InputBox(Msg, Title)

    If Me![Directory] = "" Then
    MsgBox ("Fill the Directory field with the location of your excell file, For example: C:\Documents\Excel\")
    Exit Sub
    End If

    'If EmptySearch = True Then
    'MsgBox ("Add a value before starting the search")
    'Exit Sub
    'End If

    Directory = Me![Directory]

    If Right$(Directory, 1) <> "\" Then
    Directory = Directory & "\"
    End If

    With oXL
    .Visible = False
    .Workbooks.Open (Directory & Filename & ".xls")
    End With

    DoCmd.TransferSpreadsheet acImport, cSpreadsheetTypeExcel11, "Tablename", Directory & Filename & ".xls", True, "Items!A:T"


    Set oXL = Nothing


    'Turn system messages off
    ' DoCmd.SetWarnings False

    'Turn system messages back on
    ' DoCmd.SetWarnings True
    Errhandle:
    MsgBox Err.Description
    GoTo Exit_Button_Import_Excel_Click



    Exit_Button_Import_Excel_Click:
    Set oXL = Nothing
    Exit Sub

    Err_Button_Import_Excel_Click:
    MsgBox Err.Description
    Resume Exit_Button_Import_Excel_Click

    End Sub

    However as you might recognise I don't specify the exact dept of the range. So I now import from beginning till end of file.
    However, if I'd import a text file, I can enter the exact value range from where to start and where to finish, this might be applicable to excel too I'd have to look into that as I'm facing a simular problem atm.
    Hope this helps you somewhat, I'd like to have the answer myself.

Posting Permissions

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