How to make a loop to delete a sheet if and only if the values are the same?

Question

I extract data from SAP to excel, I do this:

the transaction runs automatically in SAP GUI, then saves the data in a txt file
VBA reads my txt file
Glue it in an Excel temp sheet and then in my table
The problem is that sometimes I don't have any data, because SAP doesn't find any except that it comes to paste the data of the previous transaction (as I have several transactions to perform) what I wanted to solve my problem is that I want to make a loop "If my data in the clipboard are the same as the temp sheet, select the timesheet2 (which will be without data all the time)" and copy it into my table (which will be empty)

I'm not sure it's the right method, but I think it's feasible and it would solve the problem

Code:

   Sub StartExtract()

  ' Set the sid and client to connect to
    W_System = "P10320"
  ' Run the GUI script
    RunGUIScript
  ' End the GUI session
   objSess.EndTransaction
  'effacer contenu feuille temp
   Sheets("temp").Select
   Cells.Select
   Selection.Delete Shift:=xlUp
 ' Switch to the worksheet where the data is loaded to
  Sheets("temp").Select
   
  ' Load the CSV file
   OpenCSVFile



    Sheets("BGSOCIAL").Select
    Columns("B:G").Select
   Selection.ClearContents
   Sheets("temp").Range("B:G").Copy
   Sheets("BGSOCIAL").Range("B:G").PasteSpecial Paste:=xlPasteValues
   Sheets("BGSOCIAL").Select

If you clarify your question, or maybe include some screen shots, I'll try to help you but it's a little unclear. I'm an SAP consultant and am pretty good with Excel, and if I can't understand what you're asking, I'm guessing few others can. Good luck. Reply if you improve your question. — pgSystemTester, Jun 20 '19 at 03:48
@PGCodeRider I just want to handle the errors, if my extraction doesn't return anything "No selected data", I want the world code to keep running, but there when there is this information error the VBA code stops — XDSSIOP, Jun 20 '19 at 07:31
There is no mistake excuse me, it's just information when I run my script. "No selected data" information — XDSSIOP, Jun 20 '19 at 07:38
@PgCodeRider I accept what I didn't understand? I edited my post with the part of my code that is a problem — XDSSIOP, Jun 20 '19 at 07:58

score 0 · Accepted Answer · answered Jun 20 '19 at 08:04

You can't evaluate if the area you're copying from has values. However, I think if the Temp range is empty we can skip everything below. I'd also recommend using a Value set rather than xlPasteValues

Hope that works.

Sub StartExtract()

  ' Set the sid and client to connect to
    W_System = "P10320"
  ' Run the GUI script
    RunGUIScript
  ' End the GUI session
   objSess.EndTransaction
  'effacer contenu feuille temp
   Sheets("temp").Select
   Cells.Select
   Selection.Delete Shift:=xlUp
 ' Switch to the worksheet where the data is loaded to
     Sheets("temp").Select

  ' Load the CSV file
   OpenCSVFile

'Modified code below...


If Application.WorksheetFunction.CountA(Range("B:G")) = 0 Then
    'skips as no values in the range.

Else
'   Dont need to copy and paste, just set the values to being the same.
    Sheets("BGSOCIAL").Range("B:G").Value = Sheets("temp").Range("B:G").Value


End If

  Sheets("BGSOCIAL").Select

End Sub

How to make a loop to delete a sheet if and only if the values are the same?

1 Answers1