Importing multiple Worksheets from one Workbook

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Importing multiple Worksheets from one Workbook

Post by silverback »

I am trying to update our household accounts by putting them into an Access DB. These are currently held in multiple Excel workbooks.
Each workbook has 13 worksheets - one for each month of the year and one for totals. I do not want to import the totals worksheet.
When I import a workbook into Access, it says there are multiple worksheets and I can only pick one.
Q1. When working with a single workbook, is there a way to select multiple worksheets to import? (avoiding the need to have to manually split each workbook into 12 different files)
Q2. One of the worksheet column headings is a reserved word - Date. I am sure that in previous uses of Access when importing data, it was possible to map an Excel heading on to an Access table field as part of the import process. Is that still possible? e.g. Map the Excel 'Date' field on to the 'ActualDate' field in the table? This would save having to physically edit the heading in each worksheet (228 worksheets in total)
Thanks
Silverback

Sorry, forgot. Both Access and Excel are from Office 365.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

You can import only one sheet at a time, but you should be able to select any sheet, not just the first one.
It would be possible to create VBA code to import the sheets, but I'd have to know more about the sheet names (both the monthly ones and the totals one)
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

Unfortunately, our financial year spans the calendar year, starting in November.
Hence, for any year 'n', the sheets are Nov(n-1), Dec(n-1), Jan(n), Feb(n) etc. up to Oct(n), but without the brackets. Thus Nov18, Dec18, Jan19, Feb19 etc.
I do not want to import the totals sheet; these are going to be generated from within Access.
Many Thanks
Silverback

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

Are all the workbooks in a single folder, with no other workbooks?

And do you want to import each sheet into a separate table, or do you want to combine some or all of them?
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

The files are scattered across various folders but can easily be collected into one folder prior to importing the data into Access.
All the data is to go into one table.
Thanks
Silverback

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

Do the sheets have a column with the month or date, or should the import process fill a column to distinguish the data from each sheet?

(It might help if you attached a small sample workbook)
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

Dummy workbook attached. I hope I've anonymised it enough!
Thanks
Silverback
Dummy.xls
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

The following requires that you have already created a table named tblData with fields

PurchaseDate: Date/Time
Cost: Currency
Category: Short Text (length 255)
Description: Short Text (length 255)

And in the Visual Basic Editor, set a reference to the 'Microsoft ADO Ext. m.n for DDL and Security' library in Tools > References... (the version m.n doesn't matter)

Change the path to the folder with the workbooks.

Code: Select all

Sub ImportData()
    ' Change as needed, but keep the \ at the end
    Const strFolder = "C:\Workbooks\"
    Dim strFile As String
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim arr() As String
    Dim n As Long
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
        Set cat = New ADOX.Catalog
        cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & strFolder & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
        n = 0
        For Each tbl In cat.Tables
            If IsNumeric(Left(Right(tbl.Name, 3), 2)) Then
                n = n + 1
                ReDim Preserve arr(1 To n)
                arr(n) = tbl.Name
            End If
        Next tbl
        Set cat = Nothing
        For n = 1 To UBound(arr)
            DoCmd.TransferSpreadsheet _
                TransferType:=acImport, _
                SpreadsheetType:=acSpreadsheetTypeExcel8, _
                TableName:="tblData", _
                FileName:=strFolder & strFile, _
                HasFieldNames:=True, _
                Range:=arr(n)
        Next n
        strFile = Dir
    Loop
    CurrentDb.Execute "DELETE FROM tblData WHERE PurchaseDate Is Null", dbFailOnError
End Sub
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

Hello
Sorry for the delay in replying. The code is wonderful - the problem is 'cleaning' the data.
I have test imported a number of sheets, but there are problems, e.g. Headings on very old spreadsheets do not match headings on current spreadsheets. When I have cleaned the data, I am sure it will all be imported with no problems.
I will update when (if?) this happens. :smile:
Thanks for the VBA; wish I could write code like it/understand it!
Silverback

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

I now have lots of data in an Access table and I want to separate data for a single months. In the query, I find I have to use American date format, although the table shows dates in English format (dd/mmm/yyyy). Is there a setting that allows SQL to use English date format, please?

SELECT DISTINCTROW Sum([tblData].[Cost]) AS [Sum Of Cost], Count(*) AS [Count Of tblData]
FROM tblData
WHERE (((tblData.[purchasedate]) Between #10/1/2006# And #10/31/2006#)); returns £1230 & 58 rows. (Correct)

SELECT DISTINCTROW Sum([tblData].[Cost]) AS [Sum Of Cost], Count(*) AS [Count Of tblData]
FROM tblData
WHERE (((tblData.[purchasedate]) Between #1/10/2006# And #31/10/2006#)); returns £11191 & 488 rows. I do not spend £11k per month! :grin:
Thanks
Silverback
PS : Is there a way of asking for any rows which have a date in October rather than specifying the 'between' clause?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

SQL doesn't understand dd/mm/yyyy format. You have to use either US date format mm/dd/yyyy or ISO date format yyyy-mm-dd.

On the other hand, in design view of the query, you have to use the date format set in Windows: dd/mm/yyyy in your (and my) case.

To avoid specifying dates, you could use

WHERE Year(tblData.PurchaseDate)=2006 AND Month(tblData.PurchaseDate)=10
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

Oh, thanks Hans. The avoiding dates option is particularly useful.
Silverback :thankyou: :chocciebar:

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

I think things have changed since I last used Access, which was version 2003.
I'm trying to execute an SQL string. When I go back and look how I did this in the past, the line was "dbs.Execute strSQL, dbFailOnError"
This doesn't work in Office 365 Access - and there is no DAO to tick in Tools | References.
What's needed now, please?
Thanks
Silverback

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

In a .accdb database in Access 365, the DAO library is very intuitively named Microsoft Office Access 16.0 Database Engine Object Library. :grin:

S0010.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

This was set in the DB I'm using. I'm stuck trying to execute a string of SQL. I've tried Help, but I can't make head or tail of it. What's Transact SQL, and all help seems to point to SQL Server.
I have a variable (strSQL) containing a string of SQL in a Visual Basic module. What's the VB to execute this, please?
Silverback :scratch:

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

If you have a reference to the Microsoft Office Access 16.0 Database Engine Object Library, you should be able to use

Code: Select all

CurrentDb.Execute strSQL, dbFailOnError
assuming that the SQL is for an action query (make-table, append, update or delete).

T-SQL or Transact SQL is indeed for SQL Server, not for Access.
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

The last workbook is proving most difficult. Isn't that always the way?
Access won't import the whole workbook for that year using the code supplied above, so I tried importing individual worksheets. Four of the 12 import OK; the others fail. For the failing worksheets. . . .
1. Access says there are unacceptable words in the headings row (there is no text in any column beyond the four valid headings (PurchaseDate, Cost, Category, Description)
2. Access says there are more than 255 columns.
3. Using Ctrl+End on a failing worksheet, the highlighted cell is in row 70 (that's how many rows contain data) but it's placed in column IV

So it looks like an old problem where Excel thinks the end of the worksheet is way beyond the end of data on the worksheet. The sheet is definitely unprotected, so how can I restore the end point for this worksheet to its 'proper' place, please?
Thanks
Silverback

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

Try the following:
- Select column E (the first empty column)
- Scroll all the way to the right.
- Hold down Shift while you select column IV.
- You should now have selected columns E to IV.
- Right-click any of the column headers in the selection.
- Select Delete from the context menu.
- Save, close and reopen the workbook.
- Press Ctrl+End.
Where do you end up now?
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Importing multiple Worksheets from one Workbook

Post by silverback »

This worked. Some of the offending worksheets had extra rows as well as extra columns so I had to use this technique to get rid of them.
All data from that worksheet now in Access.
Many thanks, once again.
Silverback :thankyou: :chocciebar:

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Importing multiple Worksheets from one Workbook

Post by HansV »

Good to hear that :thumbup:
Best wishes,
Hans