Macro doesn't work on postreload

[Guidok]

Hi,

I've a macro for exporting an excel file which works fine when I run the script manually.

When I add it to a postreload trigger, it doesn't.

I've checked the document properties -- security --> Export allowed

Also under user preferences --> security --> Module and File are checked

Any idea suggestions why the macro doesn't seem to work as a postreload?

Regards,

Guido

[marcus_sommer]

I assume "locked" doesn't mean just locked else it probably changed also the applied power-settings and therefore not all features are available anymore - whereby the clipboard is in general quite sensitive ... and struggles in other situations, too. In each it's useful to re-start the machine to not searching for causes which are just temporary.

The above meant it might be an option to play a bit with the various power-settings respectively energy-profiles.

Another approach would be to try to clear the clipboard and/or to kill/start the clipboard.exe within the macro - including also some sleep-statements to give the system enough time before executing the next step - also some WaitForIdle-statement are often helpful to ensure that the UI objects are calculated and rendered before accessing them with the macro.

A further possibility would be not to copy the objects else to write them cell-value for cell-value within additionally loop-routines in the macro.

- Marcus

[Guidok]

I've tried the power settings but no luck.

I've also tried the statements below, but no luck either:

Application.DisplayAlerts = False

On Error Resume Next

qvDoc.GetApplication.WaitForIdle

Set WshShell = CreateObject("WScript.Shell")
WshShell.Run "cmd.exe /c echo. >NUL | clip", 0, True

I've seen your suggestion of the script below, but I don't know how to use it in a copy statament. When I add this, I only get an empty document.

set table = ActiveDocument.GetSheetObject( "CH02" )
for RowIter = 0 to table.GetRowCount-1
for ColIter =0 to table.GetColumnCount-1
set cell = table.GetCell(RowIter,ColIter)
msgbox(cell.Text)
next
next

[marcus_sommer]

It's not really a copying else it means to write the data and may look like:

set table = ActiveDocument.GetSheetObject( "CH02" )
for RowIter = 0 to table.GetRowCount-1
   for ColIter =0 to table.GetColumnCount-1
      set cell = table.GetCell(RowIter,ColIter)
      'msgbox(cell.Text)
      objExcel.Worksheets(1).cells(RowIter,ColIter).value = cell.Text
   next
next

- Marcus

[Guidok]

Hi,

I know have the following code...

but it says unknown runtime error at line:

objExcel.Worksheets(1).cells(RowIter,ColIter).value = cell.Text

sub Aanvullen
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set xlDoc = objExcel.Workbooks.Add
objExcel.Worksheets(1).Columns("A:C").Numberformat = "@"
objExcel.Worksheets(1).Rows("1:1000").RowHeight = 15
objExcel.Worksheets(1).Columns("A:C").Autofit
set table = ActiveDocument.GetSheetObject("CH02")
for RowIter = 0 to table.GetRowCount-1
for ColIter =0 to table.GetColumnCount-1
set cell = table.GetCell(RowIter,ColIter)
'msgbox(cell.Text)
objExcel.Worksheets(1).cells(RowIter,ColIter).value = cell.Text
next
next
xlDoc.SaveAs "X:\Uitwissel\Logistieke Controles\Aanvullen.xlsx"
xlDoc.Close
end sub

[marcus_sommer · Accepted solution]

The index in Excel is different to QV because there is no row/column = 0 else it starts with 1. Therefore add:

...
objExcel.Worksheets(1).cells(RowIter + 1,ColIter + 1).value = cell.Text
...

- Marcus

[Guidok]

Marcus, you are the man! Thanks!