Import Excel to Access (used range only)
Hiya,
I've modified the code below to import data from a pre-defined folder. The data is split into different new tables depending on the sheet name. The code works fine as it is but with one problem: it also uploads a lot of blank cells from the excel files, bumping what should be a 1000 odd row import up to well over 10000. I have attempted to amend the code to select only the used cells but with no success. I realise it is probably using .Cells(.Rows.Count, "A").End(xlUp) in some format but I cannot seem to fit it all in effectively. Any help is appreciated - Thanks! Sub Import() Dim strPathFile As String, strFile As String, strPath As String Dim blnHasFieldNames As Boolean Dim intWorksheets As Integer Dim strWorksheets(1 To 2) As String Dim strTables(1 To 2) As String strWorksheets(1) = "Airport" strWorksheets(2) = "Maritime" strTables(1) = "tblTempAirport" strTables(2) = "tblTempMaritime" blnHasFieldNames = True strPath = "F:\APRD SHARED FOLDER\Performance\" For intWorksheets = 1 To 2 strFile = Dir(strPath & "*.xls") Do While Len(strFile) 0 strPathFile = strPath & strFile DoCmd.TransferSpreadsheet acImport, _ acSpreadsheetTypeExcel9, strTables(intWorksheets), _ strPathFile, blnHasFieldNames, _ strWorksheets(intWorksheets) & "$" strFile = Dir() Loop Next intWorksheets End Sub |
Import Excel to Access (used range only)
I'm guessin you're running this from within access. To use the:
..Cells(.Rows.Count, "A").End(xlUp) you'd need to automate excel from wthin access (can be done without any issues) but would running a delete (WHERE MyField IS NULL) query after the import directly in access too much of an inefficiency? i.e., the way i see it you can still pull everything in and then get rid of what you don't need (the blanks). Perhaps it'd be more efficient not to import the blanks to begin with but then I wouldn't know how to do that without opening up excel and automating Excel from within Access. Perhaps you'd connect to the excel as a recordset as oppose to via acImport. On Oct 6, 12:30*pm, Andy wrote: Hiya, I've modified the code below to import data from a pre-defined folder. The data is split into different new tables depending on the sheet name. The code works fine as it is but with one problem: it also uploads a lot of blank cells from the excel files, bumping what should be a 1000 odd row import up to well over 10000. I have attempted to amend the code to select only the used cells but with no success. I realise it is probably using .Cells(.Rows.Count, "A").End(xlUp) in some format but I cannot seem to fit it all in effectively. Any help is appreciated - Thanks! Sub Import() Dim strPathFile As String, strFile As String, strPath As String Dim blnHasFieldNames As Boolean Dim intWorksheets As Integer Dim strWorksheets(1 To 2) As String Dim strTables(1 To 2) As String strWorksheets(1) = "Airport" strWorksheets(2) = "Maritime" strTables(1) = "tblTempAirport" strTables(2) = "tblTempMaritime" blnHasFieldNames = True strPath = "F:\APRD SHARED FOLDER\Performance\" For intWorksheets = 1 To 2 * * * strFile = Dir(strPath & "*.xls") * * * Do While Len(strFile) 0 * * * * * * strPathFile = strPath & strFile * * * * * * DoCmd.TransferSpreadsheet acImport, _ * * * * * * * * * acSpreadsheetTypeExcel9, strTables(intWorksheets), _ * * * * * * * * * strPathFile, blnHasFieldNames, _ * * * * * * * * * strWorksheets(intWorksheets) & "$" * * * * * * strFile = Dir() * * * Loop Next intWorksheets End Sub |
Import Excel to Access (used range only)
Yes, the code is within Access.
Having a massive amount of blanks isn't an issue as I intend to append the data to another table based on a unique ID. The main problem is the speed at which it uploads, especially when we need to upload many files at once. You've given me an idea though - I may see if I can get rid of any blank cells under the data in Excel that access may be wrongly perceiving as non blank, before I hit the import button on Access. I'll keep on testing - thanks. |
Import MS Excel Data INTO MS Access 2016 Database
You can check out the solution mentioned in this blog for importing up your excel spreadsheet data into Access. It has explained briefly about each and every step, so just take a look…..
How To Import MS Excel Data into MS Access 2016 Database |
All times are GMT +1. The time now is 11:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com