DoCmd.TransferSpreadsheet for a found range

[Sephaerius]

I've got several workbook containing dozens of worksheets which I need to import into an Access table. My specific stumbling block right now is that I can't quite figure out how to find and import just the range of cells I need. What I've got is on each of the worksheets, there's a table of data of varying length. It will always start in column A and end in column K, but where in column A varies by sheet. So my idea is to perform a Find and return that cell address, and then have it import the range between the found cell and K LastRow, but the syntax just isn't working on me. There's a lot going on before we get to this part of the code, but for simplicity sake I'm just focusing on where I'm stuck.



Here's what I have:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set myRng = Range("A1:A100").Find("Facility").Address

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "mm_detail", objFile, True, ws.Name & "!" & Range("myRng", "K" & LastRow)

I'm using FileSystemObject to grab the file I need for processing (objFile). I know after the ...True, is where I would define the literal range (like ...,True, "Sheet1!A23:K55") but I cannot do this, because the sheet names vary, so I'm calling it via ws.Name &"!" which works perfectly to grab each sheet as it loops.

How can I correct ..., True, ws.Name & "!" & Range("myRng", "K" & LastRow) to make it work like I am intending?

Thanks in advance,

[JoeM]

With TransferSpreadsheet, you are allowed to import named ranges. So use the VBA code to name the range in Excel, and import that named range.

[Sephaerius]

With TransferSpreadsheet, you are allowed to import named ranges. So use the VBA code to name the range in Excel, and import that named range.

Closer... this almost works, but I must be missing something still. Here's what I've added:

Dim cell As RangeDim rng As Range
Dim RangeName As String
Dim CellName As String

RangeName = "myData" & i
CellName = Range("A1:A100").Find("Facility").Address & ":K" & LastRow


Set cell = ws.Range(CellName)
wb.Names.Add Name:=RangeName, RefersTo:=cell


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "mm_detail", objFile, True, RangeName

I checked under the Name Manager and I do see that it created "myData1" and it does refer to the correct spreadsheet range I'm looking for. However when I try to import it its throwing an error that it cannot find myData1?

[JoeM]

Have a look here: https://access-programmers.co.uk/for...d.php?t=165688

Here are some other alternatives too: https://stackoverflow.com/questions/...o-not-start-at

[Sephaerius]

Okay, for the benefit of others, I was able to run down my issues here.

The first thing I happened to figure out was that the workbook had "Workbook Sharing" enabled, which was preventing the VBA running on the sheet from being picked back up. Odd, but once I disabled Workbook Sharing, the VBA started recognizing my scripted changes.

Secondly, I had to add (0,0) after .Address, so that it would take away the dollar signs for the cell address. For whatever reason, DoCmd didn't like the string to have the absolute value. So Range("A1:A100").Find("Facility").Address(0,0) also did the trick.

[JoeM]

Thanks for reporting back.

The first thing I happened to figure out was that the workbook had "Workbook Sharing" enabled, which was preventing the VBA running on the sheet from being picked back up. Odd, but once I disabled Workbook Sharing, the VBA started recognizing my scripted changes.

The devil is often in those details you never think about.

I never really did like the "Workbook Sharing" functionality. Just seems to be too problematic.
Someone did a post some time ago outlining a bunch of the issues: https://social.technet.microsoft.com...ok?forum=excel