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.

 

[Joe4]

You should be able to just take the body of the code from the one procedure, and drop it in the other, i.e.

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

 

[mamun_ges]

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.

 

[Joe4]

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?

 

[mamun_ges]

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

 

[Joe4]

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?

 

[mamun_ges]

For both files.

 

[Joe4]

I haven't recreated it all that I can test it out, by try these changes:

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

 

[mamun_ges]

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.

 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.

 

[Joe4]

When you get that error, if you click on the "Debug" button, which line of code does it highlight?