TransferSpreadsheet - Allow user to define range or highlight range

[bailey11]

Our field personnel email in a spreadsheet (several daily) that needs to be imported from excel. How do I allow the user importing these spreadsheets to define range or highlight range in excel as each file will be different range wise.

DoCmd.TransferSpreadsheet , acImport, "tblwelds", "C:\database\qryWeldInchesdaterange.xls", True

Also for my warehouse of info, is there a way to let them choose the file as well. (A browse or something?)

Thanks Baile11

 

[lars7]

You could try something like this for the first part.

DoCmd.TransferSpreadsheet acImport, 8, "Your Table", "Your File Path\Your Excel Sheet", True, "A2:X100

 

[NvrSaDi]

For starters, this will open an excel spreadsheet spreadsheet from Access 2000, identify a range name base on data in textboxes on Access form. This program requires that you save and exit.


Dim oXL As Object
Dim wrk As Object
Set oXL = CreateObject("Excel.Application")
' Open the workbook that contains the macro to run.
oXL.Workbooks.Open FileName:=Me.RicksIBS
oXL.Application.Visible = True
Set wrk = oXL.Worksheets("Detail")
ActiveSheet.Activate
ActiveSheet.Range(Me.ExcelStart & ":" & me.ExcelEnd).Select
Selection.Name = "Pending"
oXL.Quit
Set oXL = Nothing

This works for me, but you may have to polish it up for your needs. You can obviously cycle thru multiple Access records to open different Excel spreadsheets, identify different ranges and assign different rangenames.

 

[bailey11]

Won't the "A2:X100" pickup all those cells?

Some of the files will come in with A2:f4, some may be A2:f678.

I need someway for the importer to define the :F? part?

 

[NvrSaDi]

In the example the selected range is formed by 2 variables which can be taken from a form, a table, a popup box, etc...
You can fix the starting cell and make the last cell in the range a variable.

In the following example, the variables are taken from a form with textboxes named ExcelStart and ExcelEnd.

ActiveSheet.Range(Me.ExcelStart & ":" & me.ExcelEnd).Select

 

[CautionMP]

bailey11,
Here is a variant, it's a little rough (no error handling). It will open the workbook, prompt the user to select a range, then return the selection as text you could use in [tt]TramsferSpreadsheet()[/tt].

Function SelectRange(WorksheetPathName As String) As String
Dim appExcel As Object
Dim objWOrkbook As Object
Dim objRange As Object
Set appExcel = CreateObject("Excel.Application")
Set objWOrkbook = appExcel.Workbooks.Open(WorksheetPathName)
appExcel.Visible = True
Set objRange = appExcel.InputBox("Select Range", "Select Range", , , , , , 8)
SelectRange = objRange.Worksheet.Name & "!" & objRange.addresslocal
Set objRange = Nothing
objWOrkbook.Close
Set objWOrkbook = Nothing
appExcel.Quit
Set appExcel = Nothing
End Function

In Excel 2003 there is a better function than [tt]Application.InputBox()[/tt] but I don't have it loaded on this machine and can't remember what it's called.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[officeguru]

Bailey,
Are they selecting the range just to avoid importing blank rows? If that's the case you can reset the excel's last cell before importing..