Cell Range export to Text File Issue

[scwisely]

I have the below code to select a range of cells and export it to a text file; however, the cells I am selecting contain formulas and after I export and open the text file I only get "#REF..... I think I need this to maybe paste the values instead? Please help...

Sub ExportRangetoFile()'Update 20130913
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "ISM Export to Text"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Joe4]

I tried out a simple example, and it worked for me.
What program are you using to view your resulting text file?
Are there any errors in the original Excel file?
What do these formulas look like?

 

[scwisely]

Thanks Joe4! No errors when running the script and I open it using Notepad. Here's what I get when I select a couple of rows and cells. The first cells have just text (works good) ; however the remaining cells that have data validation or formulas , just show #REF !

Example:
Workflow Action Name #REF ! #REF ! #REF ! #REF ! #REF ! #REF !
Action Definition #REF ! #REF ! #REF ! #REF ! #REF ! #REF !

Thanks so much for taking a look - I'm puzzled

 

[Joe4]

Can you post what some of those formulas actually are?

 

[scwisely]

Thanks again Joe! The formulas in the cells are as follows:

Row 14 Columns E:Z =$E$4&" "&E13
Row 15 Columns E:Z =VLOOKUP(E13,Actions!$A:$E,2,FALSE)

I appreciate you looking at this are your time

 

[Joe4]

OK, so are you saying that you have this formula in cell E14:

[COLOR=#333333]=$E$4&" "&E13[/COLOR]

and this is one of the formulas returning errors?
Or is it just the VLOOKUP formulas returning errors?
Can you tell me exactly what you have in cells E4 and E13?

I am trying to recreate your exact scenario, so I can see if I can produce the same results you are seeing.

 

[scwisely]

Hey Joe,
The formulas work perfectly and no errors. In cell E4 I have the text "Clinic" in cell E13 "Patient Enters Room" so that the result would be "Clinic Patient Enters Room" and I need that result to be exported to my txt file.

THANKS A MILLION for sticking with me on this

 

[scwisely]

Also, I'd be happy to share the file with you too, if that would help - I'm at a total loss as to why this isn't working - THANK YOU!!!

 

[Joe4]

If you can upload the file to a file sharing site, and provide a link to it, I can probably take a look at it tonight when I am at home.

 

[scwisely]

THANK YOU SO MUCH!!!! I promise the file is safe - I work in healthcare IT and really could use the help. Please feel free to poke around in the file, macros or code and if you have ideas or corrections - I'd be so Thankful
here's the link

https://app.box.com/s/cptv6sfm45l7ai761rgq8v6bbeuugyqp

https://app.box.com/s/cptv6sfm45l7ai761rgq8v6bbeuugyqp

Thanks so much again!
Scott