Hi There,
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
Display More
and the .xlsx file create from A3 to last data row. I am Using below code.
Sub InventoryData_Button3_Click()
Set NewBook = Workbooks.Add
Workbooks("Support.xlsm").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 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.