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

mamun_ges

New Member
Joined
Jul 21, 2016
Messages
46
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Joe4

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

mamun_ges

New Member
Joined
Jul 21, 2016
Messages
46
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

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

New Member
Joined
Jul 21, 2016
Messages
46

ADVERTISEMENT

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

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

New Member
Joined
Jul 21, 2016
Messages
46

ADVERTISEMENT

For both files.
 

Joe4

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

mamun_ges

New Member
Joined
Jul 21, 2016
Messages
46
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
When you get that error, if you click on the "Debug" button, which line of code does it highlight?
 

Forum statistics

Threads
1,141,073
Messages
5,704,138
Members
421,328
Latest member
mippy

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