VBA does not recognize open workbook. Error which emerges is - document is out of range

[Dejan88]

Dear all,
I would like to totally automate report which I create every month. So far, I create 2 modules. First module I use to get data from SAP and second module I use to create pivot table and send data to already prepared table. Due to error (out of range) in first module, I have to stop macro and copy paste data manually in file where I run macro. Problem is that VBA does not recognize workbook which I export from SAP. I also read that there could be some issue with Excel instance. I also try with workbook as object but it doesn’t work. I would be very happy if somebody helps me to solve this issue and of course to help others if they have the similar issue. Code is down below and I also mark where issue appear. Kind regards Dean

Please Login or Register  to view this content.

[JEC.]

Maybe add the extension of the workbook?

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I have done it for you this time.)

[Dejan88]

Workbook ("Delovni list v Basis (1)") is SAP report which just pop up when I create report from SAP (i didn't find the way to save it on computer). File doesn't have any extension xls etc. but defenetly it is not csv file. Code work several times but then stop (error out of range). VBA does't recognise workbook in toggle folder and that's why I get error out of range. How can I add extension to file when I can't select it. Is solution in loop through all open workbooks?

[Dejan88]

The problem is that when I export file from SAP it is not showed in project explorer (visual basic where I run macro). However I try to loop through open workbooks it doesn't find it. What to do?

[Marc L]

Hi,

'cause the SAP file is opened in a different Excel session, not the one of your workbook
so if you know its full path name then try GetObject function as explained in VBA help …

Or the obvious is to ask on SAP side to just create the file without opening it under Excel …

[mjr veverka]

... with Excel instance ...

You will need to use API to find that instance of excel that has a window called "Delovni list v Basis (1)".
I once found a very neat Peter Thornton's code that you could use for this. Check if you can handle modifying it:

https://social.msdn.microsoft.com/Fo...forum=exceldev

[Dejan88]

Tnx. I will try.

[Dejan88]

Can you help me with instructions how to get full path name?

[jeffreybrown]

I suspect your saying the full path name of the current workbook? In the VBE, open the immediate window and type in, ?ActiveWorkbook.FullName

[Dejan88]

Tnx. I will try tomorrow at office.

[mjr veverka]

... how to get full path name?

For example: (modifications to Peter Thornton's test procedure for "Path"):

Please Login or Register  to view this content.

Full path name - with "FullName" as in info from jeffreybrown.

[Dejan88]

Tnx for help.