Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Question Unanswered: Import an Excel Workbook's Specific Worksheet

    I want to automate the importation of an Excel Workbook with multiple Worksheets. I need all the worksheets to be placed in their individual tables.

    I have tried to use macros.

    I do not program in VB, but I am a programmer. If there is good code that is documented I should be able to figure it out.

    Any ideas?

    Thank you in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    Importing Excel Workbook

    This works for me.

    Private Sub Command50_Click()

    On Error GoTo Err_Command50_Click

    Dim oApp As Object
    Dim strPath As String

    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    On Error Resume Next
    oApp.UserControl = True

    strPath = "C:\Resident Tracking\Restorative"

    Set oBook = oApp.Workbooks.Open(strPath)

    Exit_Command50_Click:
    Exit Sub

    Err_Command50_Click:
    MsgBox Err.Description
    Resume Exit_Command50_Click

    End Sub

  3. #3
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Exclamation

    Did that actually work. I don't think it's the same for SQL Server 2000, but I need to automate saving the worksheet of 3 work books into individual .csv files for upload into SQL 2000 through DTS. There end up being 32 individual worksheets, so many there is a better way? Or many I should delete null rows, and then check for null worksheets, and if they are not null then create .csv files?

    Please help, code or syntax would be great!

  4. #4
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87

    Simple method

    Hi,

    this is perhaps something similar to what you want.

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tablename", "T:\Albert\network Structure\Network Structure August 2004.xls", True, (range) "networkstructuredata!a1:j2307"

    I've used this to import multiple worksheets even multiple zones from worksheets.

  5. #5
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24
    Thanks Bert,
    but I'm actually try to separate each worksheet within the workbook in order to save them as .csv files so that I can use DTS to umport them into SQL Server 2000. Unfortunately, I'm not using MS Access anymore. So I need to be able to read individual worksheets and if they are not empty, save them to .csv files based on the worksheet.name (usiing the worksheet name as the new csv file name).

  6. #6
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    What are you using? Excel? you could use a similar method to do it with a macro.
    if this suits leave it with me and I'll look into it for tomorrow.

  7. #7
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24
    Yes, I'm using Excel 2003. And eventually the csv files will be destine for upload into SQL Server 2000 through a DTS package.

    SO I must:
    Remove null rows
    Check for Empty Worksheets
    Then Save each worksheet as an individual csv file

  8. #8
    Join Date
    Oct 2003
    Posts
    29
    Are you trying to have this in a program to import each tab (worksheet) within the workbook to be a seperate table?

  9. #9
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24
    Quote Originally Posted by bailee220
    Are you trying to have this in a program to import each tab (worksheet) within the workbook to be a seperate table?
    Yes, I understand that I may be able to run a macro that will perform the blank row deletion, which is great! Then I'd like to check for empty worksheets so that if they are blank, they do not overwrite the ones that already exist in the database. I think the export to csv file can be omitted since DTS will actually import directly from MS Excel, so now I just have to figure out how to make the macro and DTS look through all of the tabs/worksheets.

    The worksheets each represent a table in the database.

  10. #10
    Join Date
    Oct 2003
    Posts
    29
    Here's a thought. What you can do, and I've done before, is to have a worksheet in your workbook, that is a kind of driver for all your spreadsheets. In this worksheet, you have all your tab names and then like an active or inactive indicator. So like a 1 or 0 next to the tab names. The 1 can represent whether you want to import that spreadsheet. Then in Access you import this Active/Inactive worksheet first and run through it to determine which spreadsheets you want to import. Not sure if this in an option, but I thought I would present it to you. Let me know if you need any of the code to do this. Most of what I've got is actually a query that pulls out the active worksheets. But I do have some code.

  11. #11
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24
    Bailee,
    It would be helpful to see some code that reads the sheets in the workbook. It seems like everytime I nail down what they are looking for the requirement changes. Now we're trying to write the records from a query into an existing excel template (so that we can preserve the data validation, drop downs, worksheet name, and headers). I wouldn't assume that that part would be to hard, it's the import part that's really got me. Because the data has to be scrubbed for blank records, the foreign key field should not be blank, and then a query has to be run to replace the old data with the new data.
    I guess I need to study up on how ColdFusion calls or interacts with MS Excel, or should I be executing this elsewhere?
    THanks

  12. #12
    Join Date
    Oct 2003
    Posts
    29
    Here is my code that reads the different tabs in the workbook. What this actually does, is there is an import all ready complete above this code that imports this one worksheet from the workbook. And this worksheet has the other tab(worksheets) names on it. And also has whether it's active or not. Meaning whether I want to import it or not. So there's another query that only pulls the tab names from the imported worksheet that have an active status into another table. So I'm only working with the tab names that have an active status. So this is the loop that it goes through to read the tab names needed for the import, imports it, then it deletes that name from the table to continue onto the next tab name.

    Set dbs = CurrentDb
    Cnt = 0
    strSQL = "SELECT * FROM [Tab_Names]"
    Set rst = dbs.openrecordset(strSQL)
    tab_count = DCount("[Tab Names]", "Tab_Names")
    For tab_loop = 0 To tab_count - 1
    Set rst = dbs.openrecordset(strSQL)
    Set current_tab = rst![tab names]
    tab_err_flag = True

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Unformatted Upload Spreadsheet", "C:\CareNet OM Workbook.xls", True, current_tab & "!A3:IV65535"

    DoCmd.RunSQL "DELETE [_ImportErrors].Error, [_ImportErrors].Field, [_ImportErrors].Row " & _
    "FROM [_ImportErrors]" & _
    "WHERE ((([_ImportErrors].Error)=""Unparsable Record""));"

    x = DCount("error", "_importerrors")
    If x = 0 Then
    DoCmd.DeleteObject acTable, "_ImportErrors"
    Else
    MsgBox "Error in import check data."
    End If

    DoCmd.RunSQL "DELETE [Tab_Names].[Tab Names] FROM Tab_Names WHERE [Tab_Names].[Tab Names] = '" & current_tab & "'"
    Next tab_loop


    I hope this helps. Let me know if you need anything else or if I've totally confused you.

Posting Permissions

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