VBA SAP Scripts - Not recognising clipboard contents

[marc81]

Hi,

I am using VBA to get data from SAP and paste in in to Excel. Recently an issue has occurred which affects VBA recognising the clipboard contents and I get an error 'Cannot paste the data'... the code previously worked fine but now it thinks there is nothing in the clipboard. Now, the strange thing is once the code is interrupted you can press paste manually and the clipboard data is fine, it pastes no problem. I have amended my code as a workaround to end one macro after the data is copied to clipboard, and then created another macro which then will paste the data as long as the first is actually ended and VBA stopped if that makes sense. I cannot use VBA to run one after the other, the paste error occurs... the first has to end, and then I run the 2nd manually which works. The problem is some of my scripts go in and out of SAP 3 or 4 times to get different data sets and I don't really want to split them out in to 4 or 5 macros.

I don't think sharing my code will help as this now happens with numerous macros created, any which copy data from sap and try to paste.

Hope this is clear, any ideas?

 

[cbrendlinger]

I am having a similar problem. Did you find a resolution?

 

[marc81]

Hi,

I never got to the bottom of what was causing the issue so couldn't fully resolve it. Still unsure whether it was down to something that has changed in SAP or something in Excel/Windows. Other areas of my business had the same issue.

There are a couple of workarounds you could try. The data is definitely in the clipboard, but it doesn't get recognised by VBA until the macro has ended. One option is to end your macro just before the paste element, and then create another one which starts with the paste - you cannot trigger one script from the other though so it's continuous without user interaction, it has to physically stop, and then you assign another macro to a button to press or run if that makes sense. I used this option at first but because I was going back in to SAP to get data from multiple tables in the same script I didn't want to break it up in to 4 or 5 macros and have the user press a button to continue each time a paste was required - I felt it become too much.

What I ended up doing is rather than saving the SE16 data to clipboard, I downloaded it and saved it in to a temporary file on the users desktop (if the folder wasn't available I had the script create it). I then copied the data from those temporary files to my active workbook. At the end of the script I gave the user the option of they wanted to keep the raw data in the temp folder or delete it.

I used this script for assigning the temporary folder location and download filename based on time stamps;

'temp files location and names
Dim currentworkbookname, TempFilePath, User, dt, ZT60_File, VBAP_File, VBAK_File, KNA1VV_File As String

User = Environ$("Username")
TempFilePath = "C:\Users\" & User & "\Desktop\Advanced Order Model Temp Folder"
dt = Format(CStr(Now), "yyyy_mm_dd_hhmmss")
ZT60_File = "ZT60 Temp " & dt & ".txt"
VBAK_File = "VBAK Temp " & dt & ".txt"
VBAP_File = "VBAP Temp " & dt & ".txt"
KNA1VV_File = "KNA1VV Temp " & dt & ".txt"

Sap script example for downloading the SE16 data to the temp locations;

'download file to temp folder
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = TempFilePath
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = ZT60_File
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 8
session.findById("wnd[1]/tbar[0]/btn[0]").press

Once downloaded I copied it to my my file using the following example;

'COPY DATA FROM TEMP FILE TO MAIN FILE

Dim wbI As Workbook
Dim wsI As Worksheet

Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("KNA1VV") '<~~ Sheet where you want to import

Set wbO = Workbooks.Open(TempFilePath & "\" & KNA1VV_File)

wbO.Sheets(1).Cells.Copy wsI.Cells

wbO.Close SaveChanges:=False

Then at the end I used the following to clear the temp folder if required;

'check to see if user wants to delete temp files
If MsgBox("Do you want to clear your temporary downloads folder?", vbYesNo + vbQuestion, "Advanced Order Tool") = vbYes Then Kill TempFilePath & "\*.*"


Appreciate SAP can differ across businesses and what we require as an output may be quite a lot different but hope this helps. These are only snippets of my code as the whole tool I created is pretty big but may offer some ideas. Some code may not be the best way of doing things, I am pretty much a beginner when it comes to VBA.

Marc

 

[cbrendlinger]

Thank you, Marc! This information is really helpful and I appreciate you taking the time to provide an amazingly detailed response. I had thought that if I just called another macro during execution that it would be enough to stop the process but it didn't work and now I see the error of my ways. I need to stop and then restart. I do like the temporary file option although it's a bit cumbersome... but still. I am going to see what I can do to kind of copy your logic. Really, really helpful! Thanks again.