I'm reaching out with a somewhat complex issue related to a workbook called "surveyscores", which I'm hoping someone on this knowledgeable forum could assist me with. Here's what I'm dealing with:
The workbook consists of two tabs. The first one tracks our overall satisfaction scores for a specific category - a straightforward task. The second tab houses the data that I have formatted to meet my requirements using conditional formatting. The task at hand involves updating this workbook periodically. To do so, I export updated survey data from our main website every week, which includes both existing and new survey results. The goal is to import this CSV file into my current sheet, replacing old data with the new one, or perhaps only importing the new surveys.
To automate this process, I've ventured into using VBA, creating a button that imports the CSV file and handles the rest with a single click. However, I've hit a few roadblocks:
CSV Import: The current VBA script seems to struggle with complex boxes, particularly those containing extensive comments. These comments often get split into multiple cells due to the presence of commas in the text.
Data Integration: Despite successfully re-arranging the cells programmatically and deleting the unwanted columns, I've had considerable difficulty integrating the data into the table. I keep receiving an error message stating, "A table cannot overlap a range that contains a pivot table report, query results, protected cells or another table." Even attempts to resolve this, such as clearing the table data first or creating a new sheet/table programmatically, have yielded the same error.
You can find my current VBA code, along with the xlsm file and a test survey export, attached to this post.
' Show the file dialog Dim selectedFile As Variant If fileDialog.Show = -1 Then selectedFile = fileDialog.SelectedItems(1) Else Exit Sub ' User cancelled the file selection End If
' Import CSV data into a new sheet Dim importSheet As Worksheet Set importSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) importSheet.Name = "Imported Data"
With importSheet.QueryTables.Add(Connection:="TEXT;" & selectedFile, Destination:=importSheet.Cells(1, 1)) .Name = "Imported CSV Data" .FieldNames = True .RowNumbers = False .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Dim col As Long For col = UBound(deleteColumns) To LBound(deleteColumns) Step -1 importSheet.Columns(deleteColumns(col)).Delete Next col
' Step 3: Move "AV Services met your needs" to column 1 Dim avServicesColumn As Range Set avServicesColumn = importSheet.Columns("I") avServicesColumn.Copy importSheet.Range("A1") avServicesColumn.Delete
' Step 4: Clear the existing data in the "SurvTbl" table Dim tblSheet As Worksheet Dim tbl As ListObject Set tblSheet = ThisWorkbook.Sheets("Surveys") On Error Resume Next Set tbl = tblSheet.ListObjects("SurvTbl") On Error GoTo 0
If Not tbl Is Nothing Then tbl.DataBodyRange.ClearContents End If
' Step 5: Copy imported data to the "SurvTbl" table starting at A2 Dim importRange As Range Set importRange = importSheet.UsedRange importRange.Offset(1).Copy tblSheet.Range("A2")
MsgBox "CSV data has been imported and updated in the table.", vbInformation
End Sub
Any guidance, insights, or recommendations you could provide would be incredibly appreciated. I've already spent two days trying to untangle this issue, and it has left me rather frustrated. I look forward to hearing your thoughts and potential solutions.
Thank you so much in advance for your time and expertise!
@dsj7419 Well, you just discovered that VBA perhaps isn't the correct tool to extract and transform data from CSV files. Use Power Query instead. And from your file I note that you already tried that. And it produces clean data as far I could judge.
Some recommendations though. You seem to be wanting to delete most columns from the CSV. So do that first, then rename and reorder them and the last step could be to set the correct data types for the remaining columns. Your query, on the other hand, starts setting data types for all, then you rename one column, remove several columns, reorder the remaining ones and then remove most of the remaining ones. And then you load it to the Data Model and nothing more. Why not load it back to an Excel table and do the conditional formatting and analysis on that one? VBA is not needed in this case.
By the way, PQ allows you to connect to all files in a folder. So if you collect all CSV's in one folder, you can just dump new files in it, refresh the query and the table will be updated with the new data. It requires a bit of setting it up correctly, but it's not difficult.
@dsj7419 Can't replicate the issue or just don't understand. When I run your macro it imports garbage on many rows. PQ, on the other hand, gives me what seems to be a clean table with 107 rows. All nicely lined-up and long comments with with commas in them are returned in one single cell on the correct row. See example in the picture below:
@dsj7419 Why is the 'survtbl' important? Your formulas contain direct references to cells in the Survey sheet that go beyond the bounds of the table range. So you are not using structured table references.
Anyway, see if the attached file works for you. It points to the CSV on your system and should work right away.
I picked the columns that you seem to be wanting with PQ and loaded them into a set of pivot tables producing the statistics close to yours. Can't tell why they are not matching, as I didn't look into your formulas in detail. With Power Pivot you can probably do it all in one pivot table, but I didn't go so far.
The message I want to convey is that PQ and a few pivot tables (or one Power Pivot) can probably do what you need without the need of VBA and rather complicated formulas.
Unfortunately this is not the scope I was looking for. I need to do as requested. The data from the CSV needs to be imported and replaced with the data from survtbl (or added to, removing duplicates). connecting like you did is fine and it works, but doesnt do what I need it to do as its not just me using it. we need to to visually sort and import so it takes advantage of the conditional formatting and looks the same.
Maybe someone else can assist here? maybe this isnt doable?
my next plan was to move to python, but I really dont want to do that as again other people need to use it which is why I was opting for a VBA button to keep it easy.
