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

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
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.
VBA Code:
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.

VBA 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

Board Regular
Joined
Jul 21, 2016
Messages
52
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
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

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

Joe4

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

Forum statistics

Threads
1,144,582
Messages
5,725,119
Members
422,590
Latest member
Mikeyyy

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