Need VBA code and can't find one that works

[KA3PMW]

I am trying to import a CSV file into a work sheet. The file has varying length as it is created from a worksheet. The name of the file is Spotters.csv and the sheet in the workbook is named Spotters. I have searched the web for a solution and the only answer I can find loads the first line of the file an infinite number of times into the sheet. Currently the csv file has 256 lines in it but this number constantly changes as new entries are made.

Thanking you in advance for your answers.

 

[alansidman]

Look at using Power Query to import .CSV files into Excel.

 

[KA3PMW]

Look at using Power Query to import .CSV files into Excel.

 

[KA3PMW]

Look at using Power Query to import .CSV files into Excel.

I looked at it. The problem is that need to do it in a vba. The user is lucky he can turn the computer on. I do not want to run an external program. I used a vba to write the data to the Spotters.csv file. Apparently, what I want to do, read it back in, can't be done. At least I can't find anyone that knows how.

 

[KA3PMW]

I am creating the file using this code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.HasFormula Then Exit Sub
        Application.EnableEvents = False
        Target = UCase(Target.Cells(1))
    Application.EnableEvents = True
   
'/    Sub Macro2()
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Sheets("Spotters").Copy
  ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & "Spotters.csv", FileFormat:=xlCSV
  ActiveWorkbook.Close False
'/End Sub
End Sub

I can't find an example that will allow me to retrieve the data back into the original cells. I only need to do this once per session.
The CSV file grows in every time the program is run.

 

[KA3PMW]

I am creating the file using this code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.HasFormula Then Exit Sub
        Application.EnableEvents = False
        Target = UCase(Target.Cells(1))
    Application.EnableEvents = True
  
'/    Sub Macro2()
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Sheets("Spotters").Copy
  ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & "Spotters.csv", FileFormat:=xlCSV
  ActiveWorkbook.Close False
'/End Sub
End Sub

I can't find an example that will allow me to retrieve the data back into the original cells. I only need to do this once per session.
The CSV file grows in every time the program is run.

 

[KA3PMW]

Here is the code that I am using:

Sub VBA_Read_Data_Another_External_Workbook()

    '''''Define Object for Target Workbook
    Dim Target_Workbook As Workbook
    Dim Source_Workbook As Workbook
    Dim Target_Path As String
    
    '''''Assign the Workbook File Name along with its Path
    '''''Change path of the Target File name
    Target_Path = "c:\Skywarn\Spotters.csv"
    Set Target_Workbook = Workbooks.Open(Target_Path)
    Set Source_Workbook = ThisWorkbook
    
    '''''With Target_Workbook object now, it is possible to pull any data from it
    '''''Read Data from Target File
    Target_Data = Target_Workbook.Sheets(1).Cells(1, 1)
    Source_Workbook.Sheets(1).Cells(1, 1) = Target_Data
    
    '''''Update Target File
    Source_data = Source_Workbook.Sheets(1).Cells(3, 1)
    Target_Workbook.Sheets(1).Cells(2, 1) = Source_data
    
    '''''Close Target Workbook
    Source_Workbook.Save
    Target_Workbook.Save
    Target_Workbook.Close False
    
    '''''Process Completed
    
    
End Sub

It is opening the csv file but does not copy it to the spotters sheet that is in the main workbook.

 

[alansidman]

FYI: Power Query is not an external program. It is integral to native Excel and is called Get and Transform Data and found on the Data Tab of the Ribbon. Once in place, any changes to the source data can be updated by clicking on the Refresh All button on the Data Tab. Look at my signature for a better understanding of these Excel native features.

 

[KA3PMW]

FYI: Power Query is not an external program. It is integral to native Excel and is called Get and Transform Data and found on the Data Tab of the Ribbon. Once in place, any changes to the source data can be updated by clicking on the Refresh All button on the Data Tab. Look at my signature for a better understanding of these Excel native features.

The action that I need to perform must be automatic. Most of the operators are lucky they know how to turn the computer on. Also, they would not want to purchase the software.

FYI: Power Query is not an external program. It is integral to native Excel and is called Get and Transform Data and found on the Data Tab of the Ribbon. Once in place, any changes to the source data can be updated by clicking on the Refresh All button on the Data Tab. Look at my signature for a better understanding of these Excel native features.

 

[KA3PMW]

I have Power Query installed. I tried to use it and here is what happened. It allowed me to copy the data over only if the sheet did not exist. It then named the sheet as Sheet4 instead of Spotters. If the spotters sheet exists, with or without data in it, Excel won't allow me to import the file to the correct sheet and place in the sheet. If the sheet is blank, I can import the data starting at a different column..