Cell Range export to Text File Issue

scwisely

New Member
Joined
Jun 21, 2019
Messages
22
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...

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

scwisely

New Member
Joined
Jun 21, 2019
Messages
22
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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/
 

Forum statistics

Threads
1,141,707
Messages
5,707,985
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top