Save data range of a worksheet as "pipe delimited" Text file & .csv file in specific folder with specific name form worksheet.

[mamun_ges]

Hello There,

Hope all are fine. There is something that turned my head down. Please.
I have a worksheet, the data range is A3:O1000.

Now I have to save data in two formats (. text & .xlsx) in a specific location with the same file name (File Name based on cell Value= The value in A1 as Name, with C1 as Entry Number and E1 as date), I have to use one command button to do the job. The text file must create in "pipe delimited" format (no space) from range A4 to the last data row. I am using the below code for a text file, which creates a "pipe delimited" format with space.

Sub InventoryData_Button1_Click()
 Dim UsedRows As Long
 Dim UsedColumns As Long
 Dim i As Long, j As Long
 
 '// Define a suitable file name
 Open "E:\1. Inventory\Inventory.txt" For Output As #1
 With ActiveSheet
 UsedRows = .UsedRange.Rows.Count
 UsedColumns = .UsedRange.Columns.Count
 For i = 4 To UsedRows + 2
 For j = 1 To UsedColumns - 1
 Print #1, .Cells(i, j); "|";
 Next j
 Print #1, .Cells(i, UsedColumns)
 Next i
 End With
 
 Close #1
 MsgBox "Finished...", vbInformation
 
End Sub

and the .xlsx file creates from A3 to the last data row. I am Using the below code.

Sub InventoryData_Button3_Click()
 Set NewBook = Workbooks.Add
 Workbooks("Inventory.xlsx").Worksheets("Data").Range("A3:O1000").Copy
 NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
 NewBook.SaveAs FileName:=NewBook.Worksheets("Sheet1").Range("E3").Value
End Sub

The file creates but I had to save it manually to my specific location. How to merge the code into one. When the button clicks and saves two files to a specific location with a specific file name. Any suggestion or help will be highly appreciated.

Thanks in advance.

 

[mamun_ges]

You are good. The code worked as expected.
I find the place that I am missing. The file is in .xlsm format. There is an extra space in naming too.
The text file creates some extra pipe sign |||| after the last row. Why is that happening?

Appreciate the support you gave. Thanks.

 

[mamun_ges]

Oh, Yes.
You are Good ???.
The code worked as expected. I got why the error is created. The original file is in .xlsm format and there is an extra space in the naming.
I corrected it and It works like magic.??

There some extra pipe in the text file after the last row. why it is happening? Any suggestion.
I really appreciate the support you did to me.

Thanks again.

 

[Joe4]

There some extra pipe in the text file after the last row. why it is happening? Any suggestion.

Open up the Excel file that has the data, and hit CTRL+END.
Does that go to the last row of data, or past the last row?
If it goes past the last row, there may be some extra spaces or formatted rows, that make Excel think there is more data.

 

[mamun_ges]

.
I have learned lots of things from you. Thanks and best regards to you.

 

[Joe4]

You are welcome.

I see that you marked the thread as solved, but the post you marked was the post that just talked about the extra data.
Can you mark the post that best answered the initial question as the solution, so that it makes the most sense if someone reads the first post and the solution?
Thanks!