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.
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.