Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Exporting a range to a unicode txt file

0

I have this list for making keynote lists for Revit, its set up in excel and i have to export a range of it to a unicode txt film.
I need it to save to a file called keynote.txt placed in the same folder as the source exel file.
I have this code but i returns an error in the 3th line and I can't seen to figure out what the problem is.

Sub ExportRange()
Dim ExpRng As Range
Open Savefile = ThisWorkbook.Path & "\KEYNOTE.txt" For Output As #1
Set ExpRng = Worksheets("KEYNOTE").Range("A1").CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(9).Row
lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For r = FirstRow To lastRow
 Data = ""
 For c = FirstCol To LastCol
  '  data = ExpRng.Cells(r, c).Value
  Data = Data & ExpRng.Cells(r, c).Value & vbTab
Next c
Print #1, Data
Next r
Close #1
End Sub
Answer
Discuss

Answers

0
Selected Answer

Hi Rackneck and welcome to the Forum.

To get your code to work, please change the third line to remove Savefile =  (since there you're making a logical test which is False since SaveFile is not yet defined). The new line should be as in bold below:

Sub ExportRange()
Dim ExpRng As Range
Open ThisWorkbook.Path & "\KEYNOTE.txt" For Output As #1
Set ExpRng = Worksheets("KEYNOTE").Range("A1").CurrentRegion
' <<remainder of code>>

That will save your data as the file "KEYNOTE.txt" provided you have a worksheet called KEYNOTE in the workbook and have something in cell A1 (and in rows 9 down).

REVISION: Now you have attached your file, I have checked and it works fine for me. In the worksheet "KEYNOTES", I've added an orange button near cell E2 with the caption "Extract to text file"- save the revised file attached then click the button and it will run the revised macro below (changes in bold, to correct as above and to confirm that the text file was saved):

Sub ExportRange()
Dim ExpRng As Range
Open ThisWorkbook.Path & "\KEYNOTE.txt" For Output As #1
Set ExpRng = Worksheets("KEYNOTE").Range("A1").CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(9).Row
lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For r = FirstRow To lastRow
 Data = ""
 For c = FirstCol To LastCol
  '  data = ExpRng.Cells(r, c).Value
  Data = Data & ExpRng.Cells(r, c).Value & vbTab
Next c
Print #1, Data
Next r
MsgBox "Saved to " & ThisWorkbook.Path & "\KEYNOTE.txt"
Close #1

End Sub
:

Hope this works for you. If so, please don't forget to mark this Answer as Selected.

Discuss

Discussion

Thank you John_Ru I have all ready tryed that code and It came with the same error in the same line.
Rackneck (rep: 6) Aug 24, '22 at 12:29 pm
Rackneck

Odd, it worked fine for me. Did you check the conditions I said (about sheet name etc.)?

If so, please edit your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. I'll check and revise my answer/return a working file (hopefully).
John_Ru (rep: 6102) Aug 24, '22 at 12:35 pm
Also, are you running it from an Excel file which is already saved? (if not, you'll have no path)
John_Ru (rep: 6102) Aug 24, '22 at 12:39 pm
Thank you John it should all be there, so I'll upload my file. I really apriceate your help, this VBA is hard when you are starting to use it.
Rackneck (rep: 6) Aug 25, '22 at 2:08 pm
Thanks for the file, Rackneck. Please see the revision to my answer plus working file. 
John_Ru (rep: 6102) Aug 26, '22 at 2:31 am
Hi John and thank you again. What bugged me here is that you said it worked fine, and when i tested it and yout new botton I got the same error. The only difference I could think of was that I was saving it on OneDrive, so I moved the fine to a temp folder outside OneDrive and behold now it works, even the old file. Lesson learned here, nope everything works on OneDrive.  
Rackneck (rep: 6) Aug 27, '22 at 2:58 pm
Thanks for selecting my answer,  Radneck.  I rarely store files on OneDrive so didn't even suspect tgat ThisWorkbook.Path might involve it (so didn't think to test it).  Glad you have the code working now. 
John_Ru (rep: 6102) Aug 27, '22 at 3:45 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login