Take several screenshots one after another of a table

[noob342f]

Hi there,


I am trying to solve a problem with VBA and fail to solve the task.


I am looking for a VBA script to take screenshots of Range A:N


Screenshots should be taken so that all rows are included without clipping them.
And the maximum number of lines that should be displayed as a screenshot is that the line height does not exceed 150.


And new group goes to new screenshots. Group "Prio 2" should not be part of a screenshot where there is already "Prio 1".




This is a screenshot that shows what I am trying to solve:
VBAquestion.xlsxVBAquestion.xlsx



This is what I have so far:

Set oPPTApp = CreateObject("PowerPoint.Application")
oPPTApp.Visible = True


Set oPPTFile = oPPTApp.Presentations.Open("C:\Users\Me\Desktop\PPfile.pptx")
   
   Set thisRow = Sheet1.Cells(2, 4)
   Set thisColumn = Sheet1.Cells(3, 4)
       
   Set rngSource = Sheet1.Range(thisRow & ":" & thisColumn)
   rngSource.CopyPicture
   With oPPTFile.Slides(1)
   oPPTFile.Slides(1).Shapes.PasteSpecial

Can somebody please help me with this




P.S. I added my Excel file as attachment. Hope it is available for download.

[arnelgp]

it is much easier using IBM.

[georgiboy]

Hi noob342f,

Welcome to the forum

I have created the attached - it's a bit klunky but seems to get the job done, as ever there will always be different/ faster ways of doing this. Just thought I would provide something that could assist in helping you complete your task.

Hope this helps

[p45cal]

Cross posted https://chandoo.org/forum/threads/ho...ght-150.47701/

[noob342f]

Your VBA script works exactly as described! Thank you so much georgiboy for your work!

[noob342f]

The script is really brilliant. I can't believe it probably took you less than an hour to do this.

I'm having trouble converting this piece of code to my needs.

Set rng = Sheet1.Range("A4:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row)

I created a table around the data.

The table is in the range A3:N500.

The last row that has data is row ID 26.

The problem with using Cells/Range without a worksheet reference is that VBA will consider the range
on whatever it considers the active sheet.


How is it possible to let the script stop with the last not-empty row?

I found several possible solutions searching for "End(xlUp) format as table" but I am incapable to implement those solutions into this piece of code:

Set rng = Sheet1.Range("A4:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row)

I would be glad if somebody can help.

Thanks so much!

Here is the file with "format as table" VBAquestion 11.03 (1).xlsm

[georgiboy]

Try changing:

Set rng = Sheet1.Range("A4:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row)

To:

Set rng = Sheet1.ListObjects("Tabelle1").ListColumns("ID").DataBodyRange.SpecialCells(xlCellTypeConstants)

On thing I will say is that I think it would be better not to have the large blank section of the table (down to row 500) - instead trim the table to the size of the data - the table formatting will extend when you add data anyway. If you do remove the blank section of the table then you could remove the SpecialCells bit from the code to make:

Set rng = Sheet1.ListObjects("Tabelle1").ListColumns("ID").DataBodyRange

Hope this helps