Import a CSV file into excel without User prompt

I was working in an end to end automation project, where I required to import a CSV file into my existing excel workbook and name it Raw Data. based on this Raw Data sheet I intend to Draw multiple pivots. This importing of CSV and generating pivots required to be iterated for nine different regions. This is a series of Blogs through which I shall be explaining the entire automation.


So without any much delay lets start with the initial part of importing of CSV file into our excel.


Sub ImportCSV()
   
    Const strFileName = "C:\Users\AB\Downloads\Sample.csv"
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim wshT As Worksheet
   
    'Delete any Raw Data if present previously.
    Application.DisplayAlerts = False
    For Each Sheet In ActiveWorkbook.Worksheets
          If Sheet.Name = "RawData" Then
          Sheet.Delete
          End If
    Next Sheet
    Application.DisplayAlerts = True
   
    'Insert RawData from Sample.csv
    Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    Set wbkS = Workbooks.Open(Filename:=strFileName)
    Set wshS = wbkS.Worksheets(1)
    wshS.UsedRange.Copy Destination:=wshT.Range("A1")
    wbkS.Close SaveChanges:=False
    ActiveSheet.Name = "RawData"
End Sub


By this code sample we are importing Sample.csv from a specified location and placing it in our existing workbook and naming the sheet as RawData. We basically opening Sample.csv, copying data from it and then closing it.

Checking if the CSV file exists of not:

Sub ImportCSV()
   
    Const strFileName = "C:\Users\AB\Downloads\Sample.csv"
 
    If Dir(strFileName) <> "" Then
   
       
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim wshT As Worksheet
   
    'Delete any Raw Data if present previously.
    Application.DisplayAlerts = False
    For Each Sheet In ActiveWorkbook.Worksheets
          If Sheet.Name = "RawData" Then
          Sheet.Delete
          End If
    Next Sheet
    Application.DisplayAlerts = True
   
    'Insert RawData from Sample.csv
    Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    Set wbkS = Workbooks.Open(Filename:=strFileName)
    Set wshS = wbkS.Worksheets(1)
    wshS.UsedRange.Copy Destination:=wshT.Range("A1")
    wbkS.Close SaveChanges:=False
    ActiveSheet.Name = "RawData"
     
   
Else
    Exit Sub
End If

      
End Sub