asked on

Delete and Insert CSV data into Excel via VBScript

Hi Everyone,

I am using one VBS which delete and Insert records from Excel. But need some adjustments here.
For example this script deleting 10 records and inserting 10 records into Excel, but replicating some records again at right side again and again after each run.
And my Excel has 3 multiple Sheet, but I need to insert into a particular sheet i.e. Sheet2

after execution I am seeing these defects
1. Legends moved to somewhere else,
2. row 1-6 repeated but right side.

Here is my CSV data.
But after second run data got inserts some other place.

Name,Location,Phone,Comment1,Comment2,comment3
"ABC","Pune",123,"Expert Value","! Easy","Popular"
"XYZ","Kol",567,"! Expert value",Easy,"!Credit"
"PQR","Mum",234,"NOT value","Value for money","Debit"
"RST","DEL",0,"Value","NO value","N/A"
"Ram","KOL",100,"NO Value","value","N/A"
"XYZ","Kol",567,"! Expert value","!Easy","!Credit"
"qwer","DEL",567,"Expert value","Easy","!Credit"
"cvbn","Pune",567,"! Expert value","!Easy","!Debit"
"rtyu","DEL",567,"! Expert value","Easy","!Credit"
"kllo","Pune",567,"Expert value","NOT Easy","!Bad"

Open in new window

Here is my existing VBS.

srccsvfile = Wscript.Arguments(0)
tgtxlsfile = Wscript.Arguments(1)
 
'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(, "Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
  Set objExcel = CreateObject("Excel.Application")
End If
 
objExcel.Visible = False
objExcel.DisplayAlerts = False
 
'Import CSV into Spreadsheet
Set objWorkbookSrc = objExcel.Workbooks.Open(srccsvfile)
Set objWorksheetSrc = objWorkbookSrc.Worksheets(1)
Set objWorkbookTgt = objExcel.Workbooks.Open(tgtxlsfile)
Set objWorksheetTgt = objWorkbookTgt.Worksheets(1)
'Adjust width of columns
Set objRange = objWorksheetSrc.UsedRange
objRange.Borders.LineStyle = 1
Set objRangeToCopy = objRange.Resize(objRange.Rows.Count - 1).offset(1)
objWorksheetTgt.Rows(10).Resize(10).Delete
'    Range("A4:F13").Select
'    Selection.ClearContents
objRangeToCopy.Copy
objWorksheetTgt.Range("A4").Insert
 
aList = Array("NOT ", "NO ", "NONE", "!")
For Each Item In aList
            For Each c In objWorksheetTgt.UsedRange
                If InStr(1, c.Value, Item) > 0 Then
                    c.Interior.ColorIndex = 6
                End If
            Next
Next
 
'Save Spreadsheet, 51 = Excel 2007-2010
objWorkbookTgt.Save
objWorkbookTgt.Close(False)
objWorkbookSrc.Close(False)
 
'Release Lock on Spreadsheet
objExcel.Quit()
Set objWorksheetSrc = Nothing
Set objWorkbookSrc = Nothing
Set objWorksheetTgt = Nothing
Set objWorkbookTgt = Nothing
Set objExcel = Nothing

Open in new window

Here is my Command that I am running under a batch file. 2 arguments needed with path.

cscript D:\excel\wrapper.vbs \\D:\excel\Sample.csv \\D:\excel\Sample.xlsx

Open in new window

Now see the output on each run.

1st run

2nd run

3rd run

My Header and Legends should not be changed the position and data properly need to be deleted and inserted.

Neil Fleming

I think it may be as simple as this: You are deleting 10 rows starting at row 10 from the target worksheet.

objWorksheetTgt.Rows(10).Resize(10).Delete

Open in new window

But I think you want to delete 10 rows starting at row 4?

objWorksheetTgt.Rows(4).Resize(10).Delete

Open in new window

Norie

You might also want to change this,

objRangeToCopy.Copy
objWorksheetTgt.Range("A4").Insert

Open in new window

to this.

objRangeToCopy.Copy objWorksheetTgt.Range("A4")

Open in new window

Bithun Chatterjee

ASKER

Thanks Neil and Norie,
I have added both of your code.

Rows(4).Resize(10).Delete also working

Open in new window

and

objRangeToCopy.Copy objWorksheetTgt.Range("A4")

Open in new window

also added.

but after executing VBS only Legends are getting missing. all others are okay.

Bithun Chatterjee

ASKER

And see my excel has 3 sheet... i want to insert in "Report" Sheet only....
And after execution see the legends are not showing

Norie

I don't think either code suggestions would directly affect the legend.

Could you upload sample workbooks/files?

Bithun Chatterjee

ASKER

Here is the excel.
FYI my sheet in "Report" sheet. I need to add this sheet name into the code as well.
Sample.xlsx

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bithun Chatterjee

ASKER

Thanks Norie... its working now....
Only Legends parts is missing now.... anything can be done for this....??
see my Sample excel.. or above screenshot...
Now this one only pending. I don't want to touch Header 3 rows and Legends rows (line no 21,22,23).

Thanks for the help.

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bithun Chatterjee

ASKER

oh ok.. now I got the this why these got removed .. thanks for the explanation.

And yes its working now. is that possible not to mention the row number to be deleted?? is that can be dynamic?
i.e. today I have 10 rows so it is clearing 10, tomorrow if 20 rows come it should clear and insert as per input row number.
In such case Legends will be a problem so I can move Legends at top before header and will freeze it.

Finally some finishing touch.
Need to write these data in Font : ABC_Font and Size : 10
and based on some column_name need Center/Right, i.e. Name and Location value Should be "Center" and Phone no should be "Align Right".
Is that also possible??

you can send the whole code again in next post if you can, then I will mark as Best solution on that...and thanks really for helping me out .. :)

Norie

Are the finishing touches to be applied to the data that's been copied over from the CSV?

Bithun Chatterjee

ASKER

extacly yes....

Bithun Chatterjee

ASKER

I have added below code in my existing code, found in Google, but its not working. may be it won't detect the Proper range.

Range("A4:F13").Select
    With Selection.Font
        .Name = "Trebuchet MS"
        .Size = 10
    End With

objWorksheetTgt.objRange("A4:A13").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Open in new window

Neil Fleming

If the number of your rows is unknown, I'd suggest using the "currentregion" property.

eg:

'set range to currentregion
set r=objWorksheetTgt.cells(1,1).currentregion
'offset by four rows and shrink by 4
set r=r.offset(4,0).resize(r.rows.count-4,r.columns.count)
'clear
r.clear

Open in new window

Bithun Chatterjee

ASKER

any help on my Final font size change/alignment code??

Neil Fleming

You can format the destination sheet in advance, and use ClearContents and PasteSpecial to only clear and paste values, thus keeping your formatting.

Dim objWorksheetTgt As Worksheet, objRangeToCopy As Range, rTarget As Range

'define target
Set rTarget = objWorksheetTgt.Rows(1).CurrentRegion
'move down four rows and shorten by 4 rows
Set rTarget = rTarget.Resize(rTarget.Rows.Count - 4, rTarget.Columns.Count).Offset(4, 0)
'clear contents but not formats
rTarget.ClearContents
'copy source
objRangeToCopy.Copy
'paste
rTarget(1, 1).PasteSpecial xlPasteValues

Open in new window

Bithun Chatterjee

ASKER

Thanks Neil, i will test it .... but i need to format some font name with size, and align center, wrp text in the excel, can u give some help on that

Neil Fleming

If you want to format in VBA it is quite simple. For example.

rTarget.Font.Size = 10
rTarget.Font.Name = "Arial"
rTarget.HorizontalAlignment = xlCenter
rTarget.WrapText = True
rTarget.EntireColumn.AutoFit

Open in new window

If you need to format different columns within rTarget differently, you can use rTarget.Columns(1).font... etc

Joe Howard

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Norie (https:#a42229012)
-- Norie (https:#a42229131)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer