Hi,
I'm trying to import an excel file into a access table. The problem im having is that rows that have no data are being added to the table. As the amount of rows will change depending on the file, I dont want to just set a range of x amount of rows. Is there a way around this problem?
this is the code I use at the moment.
ImportName = Text1.Value
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="tblLiveData", FileName:=ImportName, HasFieldNames:=True, Range:="Sheet1!A:P"
Thanks for your help
7 1972
Hi. If there are blank rows in your Excel table the import is just faithfully replicating the blank rows. Why not import them just as they are, and once imported base all of your further processing on an Access query. The query could exclude rows automatically if you define the criteria - for example, where there is a null value in a particular field.
I recommend the 'base it on a query' approach for three reasons. Firstly, it is good practice to base forms, reports etc on queries rather than directly on the underlying table (as you have total control of the sort order and so on by doing so, and can produce views of the data that include calculated fields, say, or joined information which is not available when working directly on the table itself).
Secondly, you would have to code an import routine yourself in VBA as you would no longer be able to use the TransferSpreadsheet method you are currently using.
Lastly, the imported data would be a faithful copy of what was in Excel. I think this is also important, because if you have to compare before and after views of the data transferred for any reason you don't want to build in differences that make comparisons difficult.
You could also look at why your Excel table has blank rows in it and address that instead.
-Stewart
ps I'm assuming that you need to specify the range, as in your code you have explicitly defined rows A to P of sheet1 for import. If some of these are blank at the end, why do you need to set a row range at all? It is an optional parameter and if left off Access will simply import the whole sheet from the first to the last row.
Thanks for that,
I need the data from columns A to P, but as the number of rows in the spreadsheet will vary, I need the range to expand/decrease to include all the rows, but not the rows that dont have any data in i.e. if only 3 rows of data is input, the range would be A1:P4. I know you can create a dynamic range in excel using OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) but is there a way of using this range in access?
Any help is appreciated.
I need the data from columns A to P, but as the number of rows in the spreadsheet will vary, I need the range to expand/decrease to include all the rows, but not the rows that dont have any data in i.e. if only 3 rows of data is input, the range would be A1:P4. I know you can create a dynamic range in excel using OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) but is there a way of using this range in access?
Hi. It is possible to write a function in VBA code to open an instance of Excel, open your spreadsheet, retrieve the last cell reference, and close the file again. This could potentially be used to feed the range in your TransferSpreadsheet call. I haven't done it myself, so at this stage I have not got a function which would do so ready 'off the shelf' - perhaps other respondents might.
I would still suggest it is far simpler to use a query to exclude blank rows, however. I have to ask: how many rows of blank data are there in your sheet, and in what way would these pose any sort of a problem to Access?
-Stewart
Hi again. As a further thought, you could import the whole sheet by dropping the A:P range requirement altogether. This would mean you are not importing blank rows, but you would be importing all columns. You would then only include in your query on the base table the fields from columns A to P. Again, this is not a problem for Access.
-Stewart
Thanks for your help Stewart,
I decided on a much simpler solution, using temporary tables, append and delete queries to move the data to my main table. A dynamic range in access would have been nice, but I guess going round the houses is better than not going at all.. :)
Many Thanks for your help in this matter.
Mark
I decided on a much simpler solution, using temporary tables, append and delete queries to move the data to my main table. A dynamic range in access would have been nice, but I guess going round the houses is better than not going at all.. :)
Hi Mark. It was too intriguing a challenge to let go, so if at sometime you still want to pursue a dynamic solution here is a function which will return the last row reference of a given Excel file as a Long integer value. You can use this with your restricted column P range value as shown below.
To use it, insert the function into an existing code module in Access, or create a new module if you don't have one already. From the VB editor add a reference to the Microsoft Excel object library (Tools, References, tick Microsoft xx.x Object Library if it is not already ticked).
Back on the form in which you are doing the transfer add the following code: - Dim LastRow as Long
-
Dim strRange as String
-
LastRow = WorkSheetLastRow(ImportName) ' this must come after the ImportName assignment statement you showed in your code
-
strRange = "Sheet1!A1:P" & LastRow
-
DoCmd.TransferSpreadsheet <...others as before>, Range := strRange
- Public Function WorkSheetLastRow(Filename As String, Optional Sheetname As String = "") As Long
-
'IN: Filename (including path) of Excel file to open
-
'IN: Sheetname (optional) to open within the workbook. If not specified
-
' the default is the first sheet normally opened.
-
'OUT: the row number of the last cell in the spreadsheet.
-
On Error GoTo CantOpen
-
Dim objExcel As Excel.Application
-
Dim WS As Excel.Worksheet
-
Dim WsRange As Excel.Range
-
Dim LastRow As Long
-
Dim ErrMsg As String
-
Set objExcel = New Excel.Application
-
objExcel.Workbooks.Open (Filename)
-
If Sheetname = "" Then
-
Set WS = objExcel.ActiveSheet
-
Else
-
Set WS = objExcel.Worksheets(Sheetname)
-
End If
-
Set WsRange = WS.Range("A1").SpecialCells(xlCellTypeLastCell)
-
LastRow = WsRange.Row
-
objExcel.ActiveWorkbook.Close
-
Set objExcel = Nothing
-
WorkSheetLastRow = LastRow
-
Exit Function
-
CantOpen:
-
ErrMsg = "Cannot open File " & Filename
-
If Sheetname <> "" Then
-
ErrMsg = ErrMsg & ", Worksheet " & Sheetname
-
End If
-
Call MsgBox(ErrMsg, vbCritical, "Excel file open error")
-
Set objExcel = Nothing
-
End Function
-Stewart
For interest I also wrote a more general function to return the range in "A1:ZZnnn" format which I also include here. - Public Function WorkSheetRange(Filename As String, Optional Sheetname As String = "") As String
-
'IN: Filename (including path) of Excel file to open
-
'IN: Sheetname (optional) to open within the workbook. If not specified
-
' the default is the first sheet normally opened.
-
'OUT: the range reference to the last cell, in format "A1:XXNN" where
-
' XXNN is the last column and row in A1 to ZZ65535 format
-
On Error GoTo CantOpen
-
Dim objExcel As Excel.Application
-
Dim WS As Excel.Worksheet
-
Dim WsRange As Excel.Range
-
Dim LastRow As Long
-
Dim LastCol As Long
-
Dim Result As String
-
Dim ErrMsg As String
-
Set objExcel = New Excel.Application
-
objExcel.Workbooks.Open (Filename)
-
If Sheetname = "" Then
-
Set WS = objExcel.ActiveSheet
-
Else
-
Set WS = objExcel.Worksheets(Sheetname)
-
End If
-
Set WsRange = WS.Range("A1").SpecialCells(xlCellTypeLastCell)
-
LastRow = WsRange.Row
-
LastCol = WsRange.Column
-
objExcel.ActiveWorkbook.Close
-
Set objExcel = Nothing
-
Result = "A1:" & ColumnRef(LastCol) & CStr(LastRow)
-
WorkSheetRange = Result
-
Exit Function
-
CantOpen:
-
ErrMsg = "Cannot open File " & Filename
-
If Sheetname <> "" Then
-
ErrMsg = ErrMsg & ", Worksheet " & Sheetname
-
End If
-
Call MsgBox(ErrMsg, vbCritical, "Excel file open error")
-
Set objExcel = Nothing
-
End Function
-
Public Function ColumnRef(NumericID As Long) As String
-
'IN: valid column number for Excel worksheet
-
'OUT: string representing column reference in "A" to "ZZ" format
-
Dim Remainder As Long
-
Dim HowMany As Long
-
Dim Result As String
-
Const ChangesAt = 26
-
HowMany = Int((NumericID - 1) / ChangesAt)
-
Remainder = NumericID - ChangesAt * HowMany
-
Result = Chr(Asc("A") + Remainder - 1)
-
If HowMany > 0 Then
-
Result = Chr(Asc("A") + HowMany - 1) & Result
-
End If
-
ColumnRef = Result
-
End Function
Hi Stewart,
Thanks for that, it must have taken you ages! As I said before, I decided on a simpler solution, but will deffinately look at what you have posted as it might be a better way of doing this.
Many Thanks for your help.
Mark
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Fred |
last post by:
Hi.
How do I import while mapping an excel table to an access table please???
I've searched around and all I can find is a software product or code that
does the same thing as the access...
|
by: Fred |
last post by:
Hi.
How do I import while mapping an excel table to an access table please???
I've searched around and all I can find is a software product or code that
does the same thing as the access...
|
by: John Hewitt |
last post by:
A 'newbie' question - importing into Access from Excel. Googled the
group postings but I can't find the amswer
I have a 50 line three column file in Excel that contains Names and
Tel numbers....
|
by: deko |
last post by:
I've been trying to use the Access Import Wizard to expedite importing data
into my mdb. The nice thing about the wizard is that I can import from
different file formats - txt, xls, even Outlook -...
|
by: daniroy |
last post by:
Hello everybody, your help will be very very welcome on a very classic
question...
I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of...
|
by: Nick M |
last post by:
Hello All,
Excellent info here Thanks!
I am very new to using access in general and I am on a learning curve.
I'm trying to import an excel workbook (with worksheets) into an access db
via a...
|
by: john |
last post by:
I have 400 different Excel-spreadsheetfiles, same structure, all with only
one record in it, and all residing in the same folder. Every now and then
new Excel files are being added. In my Access...
|
by: MD |
last post by:
Hello,
I need to import a sheet of 884 different excel-file with same lay-
out.
The sheet name is 'Totaal' and is the same in all different files.
Is there a script (module) in order to:
1....
|
by: MarcGA |
last post by:
(Excel 2003, Access 2003, XP, novice user here)
I can't get Access to accept multiple Excel files to the same table. I can import the spreadsheets to a new table, but I need to import 23...
|
by: shenkel55 |
last post by:
I'm using Access and Excel 2003. Using either the import wizard or code, I have the same problem. This problem only happens with Excel files automatically generated by Corp IT. If I try to do an...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |