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]

OK, you have a lot going on there.
Walk me through an example of how you use this.
What tab are you on?
What are you entering and where?
What cells are you selecting to export?

 

[scwisely]

So I mainly use the tab "Workflow - WF Builder" - the first cell I populate is "E4" with a specific Name (e.g. clinic, OR, Oncology, etc.), Then I select items from the drop downs in Cells E13:S13 - You'll see that I use a VLOOKUP to auto populate cells in Row 15. After completing these cells I click and drag to select the Range (C14:S15) and then click on the "Export Selection Button" - which is where the problem comes in after it creates the text file , which I use Notepad to view the text file.

Thoughts?

THANK YOU!!!

 

[Joe4]

Wow! You have a lot going on here. You seem to really be pushing the limits of Excel. Quite frankly,
I don't know if I would have used Excel for a project like this - I might have chosen a erlational database program, like Access or something.

I was trying to mess around with it, but not really being familiar with it, I had a hard time manuevering around and trying to figure out what to do. Some cells got really big, renamed tabs, etc, and it was hard to see what was going on.

I am actually headed out of town for the weekend, but here is how I would probably try to approach this problem, if I were you. Start with a brand new empty file, add your export VBA code, and try it out on a very simple hard-coded section of data. If that works, add in the next layer of complexity (i.e. VLOOKUP formulas), and try exporting that. Keep adding the layers of complexity in until it matches what your program does, and keep testing the export piece with each additional layer. Then, you may be able to identify/isolate exactly what is causing the issue.

One identified, you can always post back here with what you found, and we can see if anything sticks out.

Alternatively, you could always hire a consultant to take a look at it and analyze it. https://www.mrexcel.com/consulting-services/