I am stuck, couldn't get the solution. Hope some one help me out.
I have a worksheet, the data range is A3:O1000.
Now I have to save data in two format (. text & .xlsx) in a specific location with 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 as "pipe delimited" format (no space) from range A4 to last data row.
For text file I am using the below code but It create "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 create from A3 to last data row. I am Using below code.
The file creates but I had to save manually to my desired location.
What should I have to do to change in the code to do the job as expected.
Thanks in advance.