Load a string variable inot an excel I'm creating

I'm creating an Excel spreadsheet in Access 2010 to load into a table later. The first 2 fields come from an Imported spreadsheet.  The 3rd field is the imported file name Ex "C:\All_Plans\2014_Plans.xls".
The fields populating are "FldName", "Rowvalue" (which works fine) Now I want to add the imported file name.
Here is the code:
        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").Select
            .ActiveCell.FormulaR1C1 = "FldName"
            .Range("B1").Select
            .ActiveCell.FormulaR1C1 = "rowValue"
            .Range("C1").Select
            .ActiveCell.FormulaR1C1 = "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
            .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

Open in new window

Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chuck LoweAuthor Commented:
Thanks!
I keep forgetting to do that
0
aikimarkCommented:
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?
0
Chuck LoweAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
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

Open in new window

0
Chuck LoweAuthor Commented:
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.
0
aikimarkCommented:
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.
0
Chuck LoweAuthor Commented:
@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.
0
aikimarkCommented:
What do you mean by "No SQL in Access"?
0
Chuck LoweAuthor Commented:
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.
0
aikimarkCommented:
can you create a query?
0
Chuck LoweAuthor Commented:
Yes
0
aikimarkCommented:
can you create a querydef programmatically?
0
Chuck LoweAuthor Commented:
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?
0
aikimarkCommented:
1. "No SQL" is probably referring to dynamic SQL
2. You can programmatically create a temporary query or named query, supplying the SQL.  Then turn around and invoke the query you just created.  Seems like a bit of a cheat, but it is abiding by the letter of their "No SQL" law -- you aren't directly executing the SQL.
3. For UI items, such as forms and reports, I can understand why they would want to restrict the UI data feed to querydefs.  It isn't clear why the event code can't construct and run dynamic SQL queries.
4. I haven't even gotten into domain functions.
5. You can get a query string from the SQL property of a querydef object and then manipulate it and invoke it.
6. You can get a query string from a table and then manipulate it and invoke it.

It would be helpful to understand the context of their "No SQL" rule(s).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.