Creating Named Ranges in Excel Worksheet for columns A:O

you can use the copyfromrecordset function

'here is a generic code to do this

dim rs as dao.recodrset, xlObj as object
'open the recordset

set rs=currentdb.openrecordset("query Name here")

'open the excel file

set xlobj=createobject("eacel.application")
      xlobj.workbooks.open pathx
      with xlObj
             .worksheets("xwalk").select
             .range("A1").copyfromrecordset rs
             .activeworkbook.save
       end with
       xlobj.quit

Did the answer provided fixed your issue ? or still having a problem ?
gowflow

the code from @capricorn1 works with some file and others it does not. I'm not sure as to why. Any idea?

< works with some file and others it does not>

well, you have to inform us why you think it did not  work, what is not working...

Dim VBA_16 As dao.Database
Set VBA_16 = CurrentDb

Dim arrayIDDA() As Variant
Dim rstIDDA As dao.Recordset
Set rstIDDA = VBA_16.OpenRecordset("SELECT catbTSx, PATHx FROM tblIDDA", dbOpenDynaset)

With rstIDDA
    .MoveLast
    .MoveFirst
    arrayIDDA = rstIDDA.GetRows(.RecordCount)
End With

rstIDDA.Close
    Set rstIDDA = Nothing

Dim arrayREP() As Variant
Dim rstREP As dao.Recordset
Set rstREP = VBA_16.OpenRecordset("SELECT catbTSx, PATHx FROM tblREP", dbOpenDynaset)

With rstREP
    .MoveLast
    .MoveFirst
    arrayREP = rstREP.GetRows(.RecordCount)
End With

rstREP.Close
    Set rstREP = Nothing
   
'Looping through rstGWA using arrayREP for variables

Dim rstxwalk As dao.Recordset
Set rstxwalk = VBA_16.OpenRecordset("Qry_sf133xwalk")
Dim catbTSx As String
Dim PATHx As String
Dim QRYx As String
Dim qdfQRY As dao.QueryDef
Dim rs As dao.Recordset, xlObj As Object
Set rs = CurrentDb.OpenRecordset("Qry_sf133xwalk")

For i = 0 To 55
catbTSx = arrayIDDA(0, i)
PATHx = arrayIDDA(1, i)

 Set xlObj = CreateObject("excel.application")
       xlObj.workbooks.Open PATHx
       With xlObj
              .worksheets("sf133xwalk").select
              .range("A6").copyfromrecordset rs
              .activeworkbook.Save
        End With
        xlObj.Quit
       
Next
For i = 0 To 113
catbTSx = arrayREP(0, i)
PATHx = arrayREP(1, i)

 Set xlObj = CreateObject("excel.application")
       xlObj.workbooks.Open PATHx
       With xlObj
              .worksheets("sf133xwalk").select
              .range("A6").copyfromrecordset rs
              .activeworkbook.Save
        End With
        xlObj.Quit
       
Next

End Function

^After looping through both arrays, it appears the files have been edited. When I open to verify, the data from the query has not been pasted on "sf133xwalk" in cell A6.

How would I go about creating a named range inside the For Next from A6:O5000 named "xwalk" for each individual file? I feel like this will be most effective in combination with a DoCmd export

to see if the recordset was copied to the excel file, use this codes

For i = 0 To 55
catbTSx = arrayIDDA(0, i)
PATHx = arrayIDDA(1, i)

 Set xlObj = CreateObject("excel.application")
       xlObj.workbooks.Open PATHx
       With xlObj

      .visible=true   'this will make the excel visible

              .worksheets("sf133xwalk").select
              .range("A6").copyfromrecordset rs

          stop  'this will halt the codes to execute so you can see if the records were copied. remove this later after test

              .activeworkbook.Save
        End With
        xlObj.Quit
       
Next

The recordset was copied in the first iteration, but not the second.

Thanks Rey!