Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: Importing spreadsheet issues

    I'm trying to import various spreadsheets from an excel file into an access table, I've written some VB code using the transferspreadsheet command, the problem is that I'm opening the excel files using the: "Set wb = appExcel.Workbooks.Open(filelocation)" because I have to look for a particular cell value in the worksheets, so the problem is that it only imports the first workbook that it opens. Why doesn't it import the rest of my worksheets from other spreadsheets?

    Here is part of what I have:

    Set appExcel = CreateObject("Excel.Application")

    Set wb = appExcel.Workbooks.Open("C:\22PD1206.xls")

    Call deleteLSMOldTables

    If wb.Sheets("1101_1430").Cells(6, 7) = "1" Then
    DoCmd.TransferSpreadsheet acImport, 8, "LSMNon-CriticalAlarms", "C:\22PD1206.xls", False, "1101_1430!A11:I500"
    DoCmd.SetWarnings False
    End If

    Set wb = appExcel.Workbooks.Open("C:\22PD1300.xls")
    '
    If wb.Sheets("1403_1418").Cells(6, 7) = "1" Then
    DoCmd.TransferSpreadsheet acImport, 8, "LSMNon-CriticalAlarms", "C:\Users\dmolano\Desktop\LSM Pi Data Spreadsheets\22PD1300.xls", False, "1403_1418!A11:I500"
    DoCmd.SetWarnings False
    End If

    Etc...

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Hope this helps

    I did not have my debug window opened when I wrote this in Notepad++ but here is the gist of what you should do

    You may want to recursively go through the list of excel files you are accessing and then kill and create the object in a looping fashion

    Create a table with all the excel files called tblexcelfiles with the path for each in a field called xLocation and I also assumeI assume you have dimmed the excel object with WB
    In addition add

    dim strExcelfile as string
    dim rstexcel as recordset
    Dim ws As Object
    set rstexcel = currentdb.openrecordset("select xLocation from tblExcelfiles",dbopendynaset)

    if not rstexcel.eof
    rstexcel.movefirst
    do until rstexcel.eof
    strexcelfile = rstexcel!xLocation
    Set wb = appExcel.Workbooks.Open(strexcelfile)
    'you evaluation code here to determine if you are going to import
    For example
    Set ws = wb.Sheets(1)
    If ws.Cells(1,1).Value = "Whatever" then
    Do your transfer
    End if
    'Close the workbook
    wb.Close
    Set wb = Nothing


    rstexcel.movenext
    loop
    Dale Houston, TX

  3. #3
    Join Date
    Mar 2013
    Posts
    2
    Thanks for the reply axsprog, I will give your suggestion a try tomorrow morning and let you know how it goes.

    Thanks again

Tags for this Thread

Posting Permissions

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