Importing multiple Worksheets from one Workbook
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Importing multiple Worksheets from one Workbook
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.
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.
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Importing multiple Worksheets from one Workbook
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)
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
Hans
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
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
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
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Importing multiple Worksheets from one Workbook
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?
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
Hans
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
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
All the data is to go into one table.
Thanks
Silverback
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Importing multiple Worksheets from one Workbook
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)
(It might help if you attached a small sample workbook)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
Dummy workbook attached. I hope I've anonymised it enough!
Thanks
Silverback
Thanks
Silverback
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Importing multiple Worksheets from one Workbook
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.
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
Hans
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
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.
Thanks for the VBA; wish I could write code like it/understand it!
Silverback
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.
Thanks for the VBA; wish I could write code like it/understand it!
Silverback
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
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!
Thanks
Silverback
PS : Is there a way of asking for any rows which have a date in October rather than specifying the 'between' clause?
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!
Thanks
Silverback
PS : Is there a way of asking for any rows which have a date in October rather than specifying the 'between' clause?
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Importing multiple Worksheets from one Workbook
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
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
Hans
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
Oh, thanks Hans. The avoiding dates option is particularly useful.
Silverback
Silverback
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
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
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
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Importing multiple Worksheets from one Workbook
In a .accdb database in Access 365, the DAO library is very intuitively named Microsoft Office Access 16.0 Database Engine Object Library.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
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
I have a variable (strSQL) containing a string of SQL in a Visual Basic module. What's the VB to execute this, please?
Silverback
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Importing multiple Worksheets from one Workbook
If you have a reference to the Microsoft Office Access 16.0 Database Engine Object Library, you should be able to use
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.
Code: Select all
CurrentDb.Execute strSQL, dbFailOnError
T-SQL or Transact SQL is indeed for SQL Server, not for Access.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
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
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
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Importing multiple Worksheets from one Workbook
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?
- 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
Hans
-
- 5StarLounger
- Posts: 772
- Joined: 29 Jan 2010, 13:30
Re: Importing multiple Worksheets from one Workbook
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
All data from that worksheet now in Access.
Many thanks, once again.
Silverback
-
- Administrator
- Posts: 78447
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands