Need help, new to VB - I'm not sure what is wrong...
Goal: 1. To import a CSV file into the first empty row in the spreadsheet, starting in column G. 2. Then if duplicate rows exists, delete the rows (based on two specific columns). 3. Then, copy the preexisting formula from the above cell.
Script works great the 1-2nd time, not so much the 3rd:
Run Sub 1st time - works; data imports (starting at G & at first empty row in worksheet) = good
Run Sub again - works; imports data (G & first empty row), deletes duplicate data = good
Run Sub again - Does not work; inserts a new column at G (or moves all exists data to the right), then imports that data starting a G & first empty = Not good.
I'm not sure what is causing the existing data (including the header) to be shifted one column to the right.
Note: I can run the copy/import all day long, by itself, and no issues (it keeps on copy/importing the data start at G & at the first empty row). I've only been able to narrow the issue down to at the QueryTables.Add and before the delete duplicates.
Sub Append_CSV_File_LinuxButton()
'Linux
Dim SourceCSV As Variant
Dim destSheet1 As Worksheet
Dim destCelRng As Range
'Append into sheet name at Column x, # of rows down
Set destSheet = Worksheets("LinuxWebExReport") 'Sheet name where import is going to
Set destCellRng = destSheet.Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)
csvFilePath = "c:\test\Linux\" 'Path of CSV file to import
csvFileName = "basiccsv.csv" 'Filename to import
SourceCSV = (csvFilePath & csvFileName) 'Full Path & File name.
'Import to start at # of rows down in CSV file (i.e. skip header...)
With destCellRng.Parent.QueryTables.Add(Connection:="TEXT;" & SourceCSV, Destination:=destCellRng)
.TextFileStartRow = 4
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
destCellRng.Parent.QueryTables(1).Delete
'Delete duplicate rows if the data if the 3rd and 6th column in the Array Range Match any other row
destSheet.Activate
ActiveSheet.Range("$A$3:$Q$5000").RemoveDuplicates Columns:=Array(9, 12), Header:=xlNo
'Copy Forumla from above cells
destSheet.Activate
ActiveSheet.Range("$A$3:$F$500").Formula = ActiveSheet.Range("A3:F3").Formula
End Sub
Bookmarks