Load xlsx filr from folder and write to new workbook

[manju 3]

Hi all, I need to load an Test.xlxs file form "D" drive and write the same content to new workbook in sheet1. Add sheet2 in the workbook and filter the workbook and write the filtered into sheet2 to on workbook. I have tried the below code but its writing to the same input file with sheet2 but not in the new workbook. Input file will contain

DrinkID Reciepe_Dat
2 Absolute,Enter,Test
3 Decupper,Enter,Test
4 "Absolute
"

Output is having

DrinkID Reciepe_Dat
2 Absolute
2 Enter
2 Test
3 Decupper
3 Enter
3 Test
4 "Absolute
"

but writing to the same input file. here is my code:

Sub splitbycells()
Dim splitvals As Variant
Dim totalVals As Long

Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)

sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1
'splitvals = Split(sh1.Cells(j, 2), Chr(10))
splitvals = Split(sh1.Cells(j, 2), ",")

For i = LBound(splitvals) To UBound(splitvals)
    lrow2 = sh2.Range("B65356").End(xlUp).Row
    lrow3 = sh2.Range("A65356").End(xlUp).Row
    sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
    sh2.Cells(lrow3 + 1, 2) = splitvals(i)
Next i

Next j
sh2.Range("A1") = "DrinkID"
sh2.Range("B1") = "Reciepe_Dat"

End Sub

Thanks Sharan

[Lost User]

Where is the code to write the new workbook?

[manju 3]

Hi, I have updated the code where it loads the file from some path. And setting it to myData.

Set myData = Workbooks.Open("D:\B_Test.xlsx")
Set sh1 = myData.Sheets(1)
Set sh2 = myData.Sheets(2)

Now how can i change the code so that it writes to a new workbook and and set that to myData.

Set myData = "New WorkBook"

Thanks Sharan

[Lost User]

You need to create a new Workbook, add some Worksheets and fill in their content, then save the new Workbook. Something like the following (Yes, I know it's C# but the principle is the same):

//Create an Excel workbook instance and open it
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(Type.Missing);
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Type.Missing);
excelWorkSheet.Name = "Sheet1";

int column = 1;
foreach (DataGridViewColumn dgc in dgvVolunteers.Columns)
{
// row 1 contains column headers
excelWorkSheet.Cells[1, column] = dgc.HeaderCell.Value;// copy headers from my DataGridView
++column;
}
column--; // last Excel column is one less than the grid

int row = 2; // data in Excel starts at A2
// copy from all the rows and columns in the DataGridView
// to the corresponding cells in the Worksheet
foreach (DataGridViewRow dgr in dgvVolunteers.Rows)
{
for (column = 0; column < dgr.Cells.Count; ++column)
{
excelWorkSheet.Cells[row, column + 1] = dgr.Cells[column].Value;
}
row++;
}
// Save the new Workbook
excelWorkBook.SaveAs("MyFileName.xlsx");
excelWorkBook.Close();

[manju 3]

Thanks richard. I will try it out. Thanks Manju