Import Specific Excel Worksheet from Workbook with Multiple Worksheets 1

[kjv1611]

For the life of me, I cannot get the syntax correct, it seems to me that it would be something with the Range portion of the TrnasferSpreadsheet action, but I just can't seem to get it right. If anybody knows, please let me know.

Here's what I've tried so far:

[GREEN]'This attempt was using the worksheet name as the range.[/GREEN]
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ws.Name, strWbName, True, ws.Name

[GREEN]'This was trying to append the worksheet name to the end of the workbook address:[/GREEN]
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ws.Name, strWbName & "#'" & ws.Name & "'", True

Am I missing something tiny, or am I going about it totally wrong?

I'm importing the worksheet into an existing table which was programatically made prior in the same procedure - that part worked perfectly.

I'll keep looking.

Oh, the error I'm getting is a variation of "Access cannot find the file, 'bla bla bla.'" Of course 'bla bla bla' will vary depending upon which method I attempt to use.


--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

You are missing a dollar:

ws.Name & "$

 

[kjv1611]

Okay, I thought that perhaps the whole problem was the above, but now I'm back to where I started on this point.

Now I'm getting the error message:

Run-time error '2391':
Field 'F15' doesn't exist in destination table 'TableName.'

Now this is what I thought was weird, myself, earlier. Apparently for some reason it's trying to import the column just PAST the last column of data from the worksheet, b/c there are 14 columns of data...

I suppose I have to add some further detail to the Range part which I was trying to do before.

Here's the code as it is right now:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ws.Name, strWbName, True, ws.Name & "$" & "!" & Range(A1, Cells(intMaxRow, intMaxCol))

Well.. all but the added Range attempt is what was giving me the "...F15.." error. I added in the additional range part in an attempt to fix that.

I'll keep working on it. Any suggestions on my syntax in the mean time?

By the way, the current code above is giving this error:

Run-time error '1004':
Method 'Range' of object'_Global' failed

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

Here is the odd bit, you do not need the string ($) if you are importing a range:

ws.Name & "!A1:B5"

It is best to delete the existing table if you are doing an import from scratch, or you will get data appended.

 

[kjv1611]

Well, I thought at first that had fixed it, but no, apparently it hasn't - though it's probably b/c I'm just forgetting exactly how to state the range.

Here's what I have fully:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ws.Name, strWbName, True, ws.Name & "!" & Range(A1, Cells(intMaxRow, intMaxCol))

intMaxRow and intMaxCol are the furthest out columns and rows. And actually... this is how I got those. I used the code from here:

http://www.ozgrid.com/VBA/ExcelRanges.htm

But regardless, I'd imagine I'm just goofing on the syntax. Can it be done the way I'm trying to do it?

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

You cannot use Cells(intMaxRow, intMaxCol)). You would first have to open the spreadsheet via automation and use the worksheet object, I think, to get the range.

Your initial error - F15 does not exist - can come from having not deleted the table, have you considered that?

 

[kjv1611]

Well, I originally delete all the tables, and then recreate. The whole reason for first creating the tables is so that I can specify that all fields are to be text.

As in thread705-1482903.

When I recreate the tables, I am getting the actual fields from the Excel spreadsheet, so it doesn't make sense to me that it is trying to also import a blank row. But that may perhaps have something to do with the MaxColumn/MaxRow bit I'm using from ozgrid.

At this point, though, it just seems like it's getting plain silly.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Oops, now that I look back... maybe I need to make sure the table is actually getting created... earlier it was, but now that I look at it, it isn't being created this time....

I'll check through all the code, and post back..

my goodness!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

No, the table is there, I was just missing it when looking - been looking at this whole ordeal too long... I'll work on the range part...

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, I went back to just the sheet as a range again, and I got the stinkin' ole 'F15' error again! doh!

Hmm, I've got to test something....

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

I meant to post this yesterday, but ran out of time.

Okay, to get through, I rigged up the code sort of, and it completed a handful of sheets to tables, no problem. Then it gets to one that has 15 columns with no data outside those 15 that I can see/find, and it gives an error first that says "Property not found" on the transferspreadsheet part. Then if I hit run again without changing a thing, it then says "table 'mytable' does not have field 'F27'"

Why in the world is it now looking for an F27? There is no F27 in the sheet it's coming from, and there are only 15 columns in that one... and I checked, all the columns with data were created in the table.

Tell me, is my little database possessed? that's what it is, Isnt' it?



Just for kicks, here's the whole entire procedure as it now stands:

Public Sub ExcelToDatabase()
'Code to first create tables in Access equivalent to the Excel worksheets,
'and then import the worksheets in order to possibly get around field format issues.

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As Field
    Dim c As Integer 'Excel Column
    Dim intMaxCol As Integer 'Excel worksheet furthest right column with something in it
    Dim intMaxRow As Integer 'Excel worksheet lowest row with data.
    Dim strWbName As String 'Store name of workbook
    
    Set wb = Workbooks.Open(Forms!frmImport!txtXlFile)
    Set db = CurrentDb
    
    strWbName = Forms!frmImport!txtXlFile
    
    For Each tdf In db.TableDefs
        With tdf
            If InStr(.Name, "sys") Then
            ElseIf InStr(.Name, "tbl") Then
            Else
                DoCmd.SetWarnings False
                DoCmd.DeleteObject acTable, tdf.Name
                DoCmd.SetWarnings True
            End If
        End With
    Next tdf
    
    For Each ws In wb.Worksheets
            If InStr(ws.Name, "Intro") Then
            ElseIf InStr(ws.Name, "Terms") Then
            Else
                    ws.Activate
                    intMaxCol = Excel.Cells.Find(What:="*", After:=[A1], _
                                            SearchOrder:=xlByColumns, _
                                            SearchDirection:=xlPrevious).Column
                    intMaxRow = Excel.Cells.Find(What:="*", After:=[A1], _
                                            SearchOrder:=xlByColumns, _
                                            SearchDirection:=xlPrevious).Row

                Set tdf = db.CreateTableDef(ws.Name)
                c = 1
                With tdf
                    Do While Not (ws.Cells(1, c) = vbNullString)
                       .Fields.Append .CreateField(ws.Cells(1, c), dbText, 30)
                       c = c + 1
                    Loop
                    .Fields.Append .CreateField("F" & c, dbText, 30)
                    .Fields.Append .CreateField("F" & c + 1, dbText, 30)
                    .Fields.Append .CreateField("F" & c + 2, dbText, 30)
                    .Fields.Append .CreateField("F" & c + 3, dbText, 30)
                    .Fields.Append .CreateField("F" & c + 4, dbText, 30)
                    .Fields.Append .CreateField("F" & c + 5, dbText, 30)
                    .Fields.Append .CreateField("F" & c + 6, dbText, 30)
                    
                    db.TableDefs.Append tdf
                    Set fld = Nothing
                    Set tdf = Nothing
                End With 'tdf

                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ws.Name, strWbName, True, ws.Name & "$" '"!" & Range(A1, ws.Cells(intMaxRow, intMaxCol))
            
            End If 'ws.Name
   Next ws
    
   MsgBox "Worksheets imported!", vbInformation
    
    wb.Close
    Set wb = Nothing
    db.Close
    Set db = Nothing
    
End Sub

And as for the Cells() usage in the TransferSpreadsheet line, it seems to work on the first handful of accounts, or is it just not really using that for those?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Thanks, Remou, for the tip on the $ sign.

--

"If to err is human, then I must be some kind of human!" -Me