Imported Named Range

[jgelpi16]

My dilemma is this....I want to import Columns A4:A9003 and Columns BF4:BF9003. I thought it was be as simple as the below code...However, it does not appear to be that way. Can someone help me figure this out using either a named range, or not. I don't really care, just want to be able to import two columns that do not reside next to each other. THANK YOU in advance.

    vFY = DLookup("FY", "tblFiscalCalendar", "[CalDate] = #" & vCurrDate & "#")
    vFY = Right(vFY, 2)
    vFolderLoc = "\\ncmain2\store_data$\Store Process Improvement\"
    vFileName = "Master Sales by Week NET.xls"
    vSheetRange = "''FY " & vFY & " NET'!A4:A9003," & "'FY " & vFY & " NET'!BF4:BF9003"
    DoCmd.TransferSpreadsheet acImport, , vTableName, vFolderLoc & vFileName, False, vSheetRange

[ajetrumpet]

I don't know the answer sir, but have you considered trying the "copyfromrecordset" method? Or ADO? Did you know that ADO connections to Excel treat named ranges as actual tables? I found that out when I was writing an article once.

At any rate though, that might be something to check into, but it would take a few more lines of code. Might help in the long run though...

[jgelpi16]

Thanks for the tip! I have been pulling my hair out just trying to find a starting point. I'll take a look into the "copyfromrecordset" and ADO. Thanks again.

[ajetrumpet]

also, have you looked up the transferspreadsheet method in VBA help? It should really say there whether or not excel ranges, or other similar data partials can be used as the argument. Those files usually do say things like that.

But if it says something like "arg 3: any delimited data set or table", obviously it won't work, but it'll say something!

[jgelpi16]

Didn't look up TransferSpreadsheet(), but here is my solution. Thanks for the ADO tip.

    Dim vFileName As String, vFolderLoc As String, mySQL As String, vSheetRange As String, vTableName As String
    Dim vRange As String, nameRange As String
    Dim vFileDate As Date, vCurrDate As Date
    Dim vFY As Integer, i As Integer
    Dim vStore As Integer, vAnnualSales As Double
    
'---ADO variables---
    Dim cn As ADODB.Connection
    Dim rsADO As ADODB.Recordset
    Set cn = CreateObject("ADODB.Connection")
    Set rsADO = CreateObject("ADODB.Recordset")
    
    vCurrDate = Format(Date, "mm/dd/yyyy")
    vFY = DLookup("FY", "tblFiscalCalendar", "[CalDate] = #" & vCurrDate & "#")
    vFY = Right(vFY, 2)
    vFolderLoc = "C:\Data\"
    vFileName = "Excel1.xls"
    vTableName = "FY_Annualization2"
'    vSheetRange = "''FY " & vFY & " NET'!A4:A9003," & "'FY " & vFY & " NET'!BF4:BF9003"
    
    mySQL = "DELETE * FROM FY_Annualization;"
    CurrentDb.Execute mySQL
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & vFolderLoc & vFileName & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No;"";"
        
    rsADO.Open "Select * FROM [FY " & vFY & " NET$]", _
    cn, adOpenStatic, adLockOptimistic, adCmdText
    
    i = 0
    Do While Not rsADO.EOF
        If i > 2 And i < 9000 Then
            vStore = rsADO.Fields.Item(0)
            vAnnualSales = rsADO.Fields.Item(57)
            mySQL = "INSERT INTO FY_Annualization2 ([Store], [AnnualizedSales]) VALUES (" & vStore & ", " & vAnnualSales & "); "
            CurrentDb.Execute mySQL
        End If
        rsADO.movenext
        i = i + 1
        If i > 8999 Then
            Exit Sub
        End If
    Loop