Import Excel to Access

[6360MGrist]

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

[Stewart Ross]

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.

[6360MGrist]

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.

[Stewart Ross]

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

[Stewart Ross]

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

[6360MGrist]

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

[Stewart Ross]

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

[6360MGrist]

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