Load a string variable inot an excel I'm creating

Thanks!
I keep forgetting to do that

1. If you are only assigning literal values to a range, it is a bit quicker to assign the value to the cell/range VALUE property rather than its FormulaR1C1 property
2. You might want to look at the UsedRange or CurrentRegion properties
3. You aren't saving the workbook after you have altered its content.

What is the problem you are having?

The first 2 columns are getting the header from each cell and the value until there are no more. It searches for one plan and gets all the columns and data for that plan. Consider the following spreadsheet.

Plan     Name                   Sub Plan                 ------> Column Headings
001      Fidelity                 401K                       --------> Data
002      The Principal      Profit Sharing       ---------> Data

I search for Plan 002. I bring the fields into an excel sheet with two columns.

FldName                    Row Value                -------------->  Column heading
Plan                            002                            ---------------> Data
Name                         The Principal           ----------------> Data
Sub Plan                    Profit Sharing          ----------------> Data

I want to ad a 3rd column "Import File which has the excel file name that I imported the data from. I need to know the exact code to add the value. So it looks like this.

FldName                    Row Value               Import File              --------------->  Column heading
Plan                            002                           c:\PS_401K.xls         ---------------> Data
Name                         The Principal          c:\PS_401K.xls         ----------------> Data
Sub Plan                    Profit Sharing         c:\PS_401K.xls         ----------------> Data

I think it will look something like this:

        Set SearchRange = .Range(Cell_Start, .Range(Cell_End).End(xlUp))
        Set FindRow = SearchRange.Find(g_strPlanID, LookIn:=xlValues, lookat:=xlWhole)
        lrow = FindRow.Row
        
        If lrow > 0 Then
            strRange = "1:1," & Trim(Str(lrow)) & ":" & Trim(Str(lrow))
            .Range(strRange).Select
            .Selection.Copy
            .Workbooks.Add
            .Range("A2").Select
            .Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=True
            .Range("A1").Value = "FldName"
            .Range("B1").Value = "rowValue"
            .Range("C1").Value = "ImportFile"
            .ActiveWorkbook.SaveAs strSaveAsFile
            .Sheets("Worksheet1").Select
            'find the last row of data so that blank rows are not imported
            lrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("Worksheet1").Range(.Sheets("Worksheet1").Cells(2, 3), .Sheets("Worksheet1").Cells(lrow, 3)).Value = ActiveWorkbook.Name
            .ActiveWorkbook.Close False 'Close the Transposed Data

'I added "ImportFile" as my C column.
'Here is the transfer code

    '---Set the range to import into access (trims the empty lines)
        strImportRange = "Sheet1!A1:D" & Trim(Str(lrow))

        acApp.OpenCurrentDatabase DB_PATH, False, DB_PWD
         
        acApp.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Plan_Staging", strSaveAsFile, True, strImportRange

Select allOpen in new window

Thanks for the work on this but I do not see where I would put the field that contains the name of the file.
For instance   g_ImportFile = "c:\PS_401K.xls" . The variable g_ImportFile will change with each file i extract from.

The code I posted puts the name of the file in the Excel workbook, which you would see in your table (post import).  Normally, I would run the import and run an Update query, which sets the file name in every place that the filename field is Null.

@aikimark
I don't see it but I trust you. LOL. I'll go thru the debugger to see where it is. My experience is more with straight transfers of all columns in a table so this had me a little baffled. But being the first time I coded this from scratch (except for help from a reference manual) I was feeling proud of myself. Then the user added this wrinkle to it.
My thought also was to run an Update Query afterwards but being a contractor I have to abide by their strict standards. One of which is no SQL in Access. We can only use queries and pass along Parameters as needed.

I'll implement it on Monday.

I appreciate you helping me out especially on a weekend.

What do you mean by "No SQL in Access"?

Just that. No SQL on a form, macro or function .
No SELECT , INSERT, UPDATE, DELETE. We can open/close the Database (using ADO) but all SQL has to be in queries with Parameters passed (if needed).  Also no Bound forms. All forms are loaded with SQL queries.  Takes some getting used to.

can you create a query?

Yes

can you create a querydef programmatically?

They currently do not do it that way. But given a good reason I'm sure I can talk them into it.
What are you driving at?

I've requested that this question be closed as follows:

Accepted answer: 500 points for aikimark's comment #a40953131

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.