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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
You should be able to just take the body of the code from the one procedure, and drop it in the other, i.e.
Rich (BB code):
Sub InventoryData_Button1_Click()

'***FIRST BLOCK***
 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

NewBook.Close

'***SECOND BLOCK***
 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
 
Upvote 0
Thanks for the response. The code worked fine with only one query. Where the .xlsx file is saving? I couldn't find the file-saving location.

When I click the command button twice it opens a file name that is not written on the cell E3.
 
Upvote 0
What is in "NewBook.Worksheets("Sheet1").Range("E3")"?
If there is no file path, it is probably saving it to the default file path.
Where do you want it saved?
 
Upvote 0
Can you tell me what to change in code to save the excel sheet as a new workbook with the name of the active worksheet and save it where the text file saves "E:\1. Inventory\".
It's my ignorance that I am not unable to use the code the right way.

Thanks and best regards
 
Upvote 0
Can you tell me what to change in code to save the excel sheet as a new workbook with the name of the active worksheet and save it where the text file saves "E:\1. Inventory\".
It's my ignorance that I am not unable to use the code the right way.
For which file?
The text file or the Excel file?
 
Upvote 0
I haven't recreated it all that I can test it out, by try these changes:
VBA Code:
Sub InventoryData_Button1_Click()

Dim fPath As String
Dim fName As String

'Set file path
fPath = "E:\1. Inventory\"

'Get file name for name of active sheet
fName = ActiveSheet.Name

'***FIRST BLOCK***
 Set NewBook = Workbooks.Add
 Workbooks("Inventory.xlsx").Worksheets("Data").Range("A3:O1000").Copy
 NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
 NewBook.SaveAs Filename:=fPath & fName & ".xlsx"
NewBook.Close

'***SECOND BLOCK***
 Dim UsedRows As Long
 Dim UsedColumns As Long
 Dim i As Long, j As Long
 
 '// Define a suitable file name
 Open fPath & fName & ".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
 
Upvote 0
Solution
I am really messed up and maybe boring to you. Because I made some changes in the original file. That's why it causes a problem.

It says" Run-Time error "9"
Subscript out of range.

May the problem is in the below code which maybe not matched my current workbook.

VBA Code:
 Set NewBook = Workbooks.Add
 Workbooks("Inventory Data.xlsx").Worksheets("Inventory Data").Range("A3:O1000").Copy
 NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
 NewBook.SaveAs Filename:=fPath & fName & ".xlsx"
NewBook.Close

My Workbook name is "support.xlsm"
My worksheet name is Inventory
My file save location is E:\Inventory\
The File save as "pipe delimited" Text file & .xls file format with the same name of the worksheet is "Inventory"
The text file data will be the range from "A4:O1000" whereas the .xlsx Data will be from "A3:O1000"

Hope you support me and understand my ignorance.
 
Upvote 0
When you get that error, if you click on the "Debug" button, which line of code does it highlight?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top