Importing Range of Cells

  • Thread starter Thread starter matthew
  • Start date Start date
M

matthew

I have a client who emails orders in an excel
spreadsheet. There is a messy heading on all these sheets
so I want to just import the data using import a range.
The problem is that the range changes depending on the
amount of orders on the sheet. Is there a way to automate
the range import to import records until it gets the last
order in which the next row is blank? Any help or
direction would be great. thank, and enjoy your day.
matthew
 
You can import a named range using TransferSpreadsheet.
The trick is: Who will name the range and when?

If it is the person sending you the file then you can automate your process.
If it is *you*, then you have to open the file and name the range before
importing it.
 
Hi Matthew,

I've pasted a VBA function at the end of this message that may be
helpful.

You pass it details of the workbook and worksheet and the top left cell
of the data area, and it creates a named range covering the contiguous
block of populated cells starting with that one. Usually this covers the
range of data, but if your worksheets are particularly gnarly you'll
need to modify it to examine cell contents as it expands the range.


I have a client who emails orders in an excel
spreadsheet. There is a messy heading on all these sheets
so I want to just import the data using import a range.
The problem is that the range changes depending on the
amount of orders on the sheet. Is there a way to automate
the range import to import records until it gets the last
order in which the next row is blank? Any help or
direction would be great. thank, and enjoy your day.
matthew
Click to expand...


Function DefineExcelRange(Workbook As String, Worksheet As String, _
RangeName As String, FirstCell As String) As Boolean

Dim oXL As Excel.Application
Dim oWbk As Excel.Workbook
Dim oWks As Excel.Worksheet
Dim xRa As Excel.Range
Dim blExcelIsMine As Boolean

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
blExcelIsMine = False
Err.Clear
If IsNull(oXL) Then
Set oXL = CreateObject("Excel.Application")
blExcelIsMine = True
If Err.Number > 0 Then
MsgBox "Sorry, couldn't launch Excel", _
vbOKOnly + vbExclamation
Exit Function
End If
End If
On Error GoTo 0

Set oWbk = oXL.Workbooks.Open(Workbook)
Set oWks = oWbk.Worksheets(Worksheet)
Set xRa = oWks.Range(FirstCell)
'Expand range
Set xRa = oWks.Range(xRa, xRa.End(xlToRight).End(xlDown))
xRa.Name = RangeName

Set xRa = Nothing
oWbk.Close True 'save changes
Set oWbk = Nothing
If blExcelIsMine Then oXL.Quit
Set oXL = Nothing

End Function
 
I'm new to excel vba, so maybe i'm missing something, but how would you
apply this in a module? if the function is returning the range, how would
it work as boolean?
 
I'm new to excel vba, so maybe i'm missing something, but how would you
apply this in a module? if the function is returning the range, how would
it work as boolean?
Click to expand...

Just put it in an ordinary module (in Access).

It doesn't return a range, it identifies it and names it. But I got
distracted while writing the code and forgot about actually setting the
return values. The idea was to return True if the range was named, and
False if anything went wrong. Here's a version that does that:



Function DefineExcelRange(Workbook As String, Worksheet As String, _
RangeName As String, FirstCell As String) As Boolean

Dim oXL As Excel.Application
Dim oWbk As Excel.Workbook
Dim oWks As Excel.Worksheet
Dim xRa As Excel.Range
Dim blExcelIsMine As Boolean

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
blExcelIsMine = False
Err.Clear
If IsNull(oXL) Then
Set oXL = CreateObject("Excel.Application")
blExcelIsMine = True
If Err.Number > 0 Then
MsgBox "Sorry, couldn't launch Excel", _
vbOKOnly + vbExclamation
Exit Function
End If
End If
On Error GoTo ErrHandler:

Set oWbk = oXL.Workbooks.Open(Workbook)
Set oWks = oWbk.Worksheets(Worksheet)
Set xRa = oWks.Range(FirstCell)
'Expand range
Set xRa = oWks.Range(xRa, xRa.End(xlToRight).End(xlDown))
xRa.Name = RangeName

DefineExcelRange = True 'Success

NormalExit:
Set xRa = Nothing
oWbk.Close True 'save changes
Set oWbk = Nothing
If blExcelIsMine Then oXL.Quit
Set oXL = Nothing
Exit Function

ErrHandler:
DefineExcelRange = False 'Failure
Err.Clear
Resume NormalExit:

End Function


To use it you'd do something like

Dim strWbk As String
Dim strSheet As String
Dim strRange As String

strWbk = "D:\Folder\File.xls"
strSheet = "Sheet1"
strRange = "RangeImport"
strFirstCell = "A5"

If DefineExcelRange(strWbk, strSheet, _
strRange, strFirstCell) = True Then
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "tblT", strWbk, _
False, strRange
Else
MsgBox "Couldn't import data"
End If
 
Back
Top