Excel Automation with StudioX - Practice - Books Sales Report - Part 2

Learn Academy Feedback
1

This course is designed so you can also learn from your fellow learners, particularly through the forum topics.

If you need help with Part Two of the Book Sales Report practice, post a question as a reply below. To increase your chances of being helped, be as descriptive as possible. Include in your comment:

  • A description of your issue: when is it happening, what activity you have trouble with.
  • A screenshot of your error.
  • You can also attach your automation project.

If you can help a fellow learner, don’t be afraid to reply and make a suggestion. Participating in the conversation helps solidify the knowledge you’ve acquired in this course.

3

Hello!

I noted that the “weekly sales” Excel file for Books Sales Report - Part 1 and 2 does not match with what is shown in the training content.

The downloadable content has 4 sheets: “Week 7, 8, 9 and 10”, but Weeks 1, 2, 3, 4, 5 and 6 are missing.

image
image1920×1019 122 KB

Regards!

4

The RobotPath is bit misleading.

image

This cell reference is leading to the text in the last row (week 6) being replaced by the new value (week 7). Instead we should add a step where we set variable value to Last Row + 1 in order to copy the new rows after the existing ones.

5

This is correct. I was wondering why week 6 kept getting replace week 7’s data.

I downloaded the solution provided and the result was still the same. Was pretty confusing to me at first glance.

6

Hi ,

I try to fix the problem by using “set Variable Value” Activitiy

But i faced an issue that the result is in text format it shows “7+1” rather 8

Please adives

16683655119348012960550850737544
166836551193480129605508507375441920×1440 123 KB

7

Good Course.

8

I just had the same issue – the solution is visible in the previous screenshot.

The “Configure last row as” dropdown should be “First empty row” instead of “Last populated row”. This is actually incorrect in the solution project .zip as well and needs to be fixed.

9

Make sure that everything is numeric. I don’t think you get an error if it is text but it doesn’t work.
It worked fine to me. I hope my print screens help:

image
image777×274 10.3 KB

10

For the first “Find First/Last Data Row” They incorrectly set “Configure last row as” as “Last populated row” when it should be “First Empty Row”

11

I don’t know if it is because I am creating automation in Studio X 08.2023 but my automations just don’t run. Constantly running into errors. Cannot create Input copy folder from the beginning and running into all sorts of errors. Been going at these 2 practices for like 2 hours and just cannot figure it out.
:weary: :rage:

Screenshot 2023-08-16 at 5.30.20 PM
Screenshot 2023-08-16 at 5.30.20 PM2128×282 49.2 KB

12

Then I open solutions project, runs fine. Try to copy and paste certain similar paths, and run into errors. I… give up…

13

Anyone faced Problem with the data of WorkSheet8 ? it shows blank cell in output file.

14

Suggestions:

  1. Be sure that 2.1 Find First/Last Data Row - Book Inventory has 'Configure last row as ‘First empty row’.

  2. Open input file BookInventory.xlsx. Select SalesOverview sheet and confirm Column A ‘Week’ is formatted as Text. And, confirm Column B ‘Sales’ is formatted as Number. Turn off auto save for the file.

  3. Open input file WeeklySales.xls Select each sheet (‘Week 7 through Week 10’) and confirm Column F ‘Sum of Price’ is formatted as Number. Turn off auto save for the file.

  4. Confirm 2.2.1.2 Write Cell - Week Name is configured with the following:
    What to Write
    CurrentSheet.Name
    Where to write
    BookInventory.Sheet(“SalesOverview”).Cell(string.format(“A{0}”, LastRowIndex_BookInventory.ToString))

  5. Confirm 2.2.1.3 Write Cell - Week’s Total is configured with the following:
    What to Write
    CurrentSheet.Cell(string.Format(“F{0}”, (“LastRowIndex_WeeklySales”).ToString))
    Where to write
    BookInventory.Sheet(“SalesOverview”).Cell(string.Format(“B{0}”, (“LastRowIndex_BookInventory”) ToString))

  6. Delete any files in your ‘Input Files - Copy’ Folder before you attempt to ‘Run’ again.
    Good Luck!!!

p.s…I did not format a ‘Set variable value activity’ as others did.

15

Hi Kennedy,

I keep getting this error message for the last Write Cell activity, even after following the steps you described above:

"Activity Write Cell - Week’s total (Write Cell X) failed:

The range F38 does not exist.
Exception from HRESULT: 0x800A03Ec"

Note: if I skip one error, then it get another error with another F cell. I removed the blank row in the pivots, but still got the same error message. Any advise?

16

Good morning,

  Rumana, Do you have your project set to Windows or Windows Legacy?  I utilized Windows for this project.

   *I found a typo in my suggestion above (#5):  LastrowIndex vs. LastRowIndex.

Confirm 2.2.1.3 Write Cell - Week’s Total is configured with the following:
What to Write*
CurrentSheet.Cell(string.Format(“F{0}”, (“LastRowIndex_WeeklySales”).ToString))
Where to write*
BookInventory.Sheet(“SalesOverview”).Cell(string.Format(“B{0}”, (“LastRowIndex_BookInventory”) ToString))

Michelle Kennedy

17

Hi Michelle,
I copied mine from the robot path, so it was correct:

CurrentSheet.Cell(string.Format(“F {0}”, LastRowIndex_WeeklySales.ToString))

I am using Windows latest version.

18

Rumana,

  What version of StudioX are you using? I am using StudioX 2023.10.0.  Do you have BookInventory and WeeklySales within Data Manager as Resources?

Michelle

19

Hi Michelle,
I am using StudioX 2022.4.1 enterprise version. I do have both of those files under resources.

I think there is something within the files that is causing this error. At first, I thought it was the Blank at the bottom of the pivot as the F cell #s were resembling those cells so I check off blank, but still got the same error. No one seem to have got this error in this thread, so I was litlle disappointed :slight_smile:

20

I think the issue is, for 2.2.1.3 Write Cell - Week’s total: the training Workflow used the following:
for “What to write”:
CurrentSheet.Cell(string.Format(“F{0}”, Saved.Values(Of System.Int32)(“LastRowIndex_WeeklySales”).ToString))

and for “Where to write”:
BookInventory.Sheet(“SalesOverview”).Cell(string.Format(“B{0}”, Saved.Values(Of System.Int32)(“LastRowIndex_BookInventory”).ToString))

If I copy paste this to my workflow, I get an Error message saying “The Save is not declared”.

The Robot Path has a side note saying for this two “LastRowIndex_WeeklySales” variables, we would need to do a Custom Input. The Training WF, has the Custom input as follows:

“F LastRowIndex_WeeklySales”

Where the ‘F’ in front of “LastRowIndex_WeeklySales” is creating the “Saved.Values(Of System.Int32)”

This function does not match with what you described in # 5.

This is what causing my workflow to fail at this step.

Can anyone help with this?

Thank you!!