Copy Range frome book 2 book Excel

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
toopnad
Junior Coder
Posts: 34
Joined: Mon Apr 12, 2021 10:10 am
Location: Russian Totalitarian Republic, Perm city

Copy Range frome book 2 book Excel

Post by toopnad » Mon Aug 02, 2021 1:08 pm

I need to copy the specified range from one Excel book to the first empty table row of another book

Code: Select all

VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename,,False,,,123)
end sub
VBEND
Let>GFL_TYPE=0
Let>GFL_SORTTYPE=1
IfFileExists>Z:\Обмен\ЗАЯВКИ\ЗАЯВКИ в поставку\ОТЕЧ\2021\*.*
GetFileList>Z:\Обмен\ЗАЯВКИ\ЗАЯВКИ в поставку\ОТЕЧ\2021\*.*,files,;
Separate>files,;,file_names
MessageModal>Num Files: %file_names_count%
Let>k=0
Repeat>k
  Let>k=k+1
  Executefile>file_names_%k%
  //Message>file_names_%k%2
  //wait>15
    //Extract the file name from the entire path/filename
  ExtractFileName>file_names_%k%,oldfilename,1
  Let>WF_TYPE=2
  WaitWindowOpen>%oldfilename% - Excel
  Let>regPath=Z:\Обмен\ЗАЯВКИ\Архив\Реестр\Реестр отеч.xlsx
  XLOpen>regPath,1,xlBook
  Let>WF_TYPE=2
  WaitWindowOpen>Реестр отеч - Excel
    Let>k1=0
    Label>scan
    Let>k1=k1+1
    DDERequest>Excel,regPath,R%k1%C1,result,0
    If>result<>CRLF,scan
      //So k is first empty cell, put clipboard contents in it
      DDERequest>Excel,file_names_%k%,R2C3,firm,0
      DDERequest>Excel,file_names_%k%,R3C3,date,0
      DDERequest>Excel,file_names_%k%,R5C3,contragent,0
      let>k2=k1-1
      let>lastNum="=R[-1]C3+1"
      //DDERequest>Excel,regPath,R%k2%C3,lastNum,0
      DDEPoke>Excel,regPath,R%k1%C1,firm
      DDEPoke>Excel,regPath,R%k1%C2,date
      DDEPoke>Excel,regPath,R%k1%C3,lastNum
      DDEPoke>Excel,regPath,R%k1%C4,contragent
      DDERequest>Excel,regPath,R%k1%C3,num,0
      DDEPoke>Excel,file_names_%k%,R4C3,num
      XLSave>xlBook,regPath
      XLQuit>xlBook
      VBRun>OpenExcelFile,Z:\Обмен\Поставка\Цены+Учет\УЧЕТ\Учет движения поставок ПЗПК роботизированный.xlsx
      Let>WF_TYPE=2
      SetFocus>%oldfilename% - Excel
       Let>k3=7
    Label>scan2
    Let>k3=k3+1
    DDERequest>Excel,file_names_%k%,R%k3%C4,result,0
    If>result<>CRLF,scan2
    SetFocus>Учет движения поставок ПЗПК роботизированный - Excel
    Let>k4=k3-1
    let>range="R8C4:R%k4%C15"
    //HELP HERE!!!
    //I need to copy the specified range from one Excel book to the first empty table row of another book
    
    Until>k,file_names_count
else
//Отправить письмо что нет заявок
EndIf

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Copy Range frome book 2 book Excel

Post by Dorian (MJT support) » Mon Aug 02, 2021 3:15 pm

Those who know VBA can probably give a VBA example which uses VBA to paste to the next blank row, but using a mixture of simple VBA to copy and paste a range, and Macro Scheduler to find the first blank row, the standalone example below will copy a range from Sheet1 and paste it into the first blank row on Sheet2.

Resources :
XLRunCode

Recommended reading : XLGet XLGetCell XLSetCell

Link to Excel Sheet

Note:
For XLRunCode to work 'Trust Access to the VBA project object model' must be enabled in Excel:

In Excel go to File/Options -> Trust Center -> Trust Center Settings -> Macro Settings.
Under 'Developer Macro Settings' enable 'Trust Access to the VBA project object model'

Code: Select all

//Open the Workbook
XLopen>d:\PasteNewSheet.xlsx,1,xlh

//Get Dimensions of Sheet2 (target sheet)
XLGetSheetDims>xlh,Sheet2,MaxRow,MaxCol

//Find first blank row
Let>PasteRow=MaxRow+1
Let>PasteCell=A%PasteRow%

//Read Copy/paste VBA
LabelToVar>CopyRange,CopyCode
LabelToVar>PasteRange,PasteCode

//Execue Copy/Paste VBA
XLRunCode>xlh,CopyCode
XLRunCode>xlh,PasteCode

/*
CopyRange:
Sheets("Sheet1").Select
Range("A2:C2").Select
Selection.Copy
*/


/*
PasteRange:
Sheets("Sheet2").Select
Range("%PasteCell%").Select
ActiveSheet.Paste
*/

Yes, we have a Custom Scripting Service. Message me or go here

toopnad
Junior Coder
Posts: 34
Joined: Mon Apr 12, 2021 10:10 am
Location: Russian Totalitarian Republic, Perm city

Re: Copy Range frome book 2 book Excel

Post by toopnad » Mon Aug 02, 2021 3:53 pm

Thanks, but this is impossible, because the file where the lines need to be inserted has a password for editing. I cannot open it with Scheduler commands. I am using VBA to open it. I could simulate human actions using hotkeys, but I want optimized and fast code. I can write VBA code for Excel if you help me adapt it for Macro Scheduler.

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Copy Range frome book 2 book Excel

Post by Dorian (MJT support) » Mon Aug 02, 2021 4:26 pm

You don't need to adapt VBA for Macro Scheduler. Simply use XLRunCode and the VBA you've written. Or are you saying you can't use XLRunCode? Apologies if I am misunderstanding.
Yes, we have a Custom Scripting Service. Message me or go here

toopnad
Junior Coder
Posts: 34
Joined: Mon Apr 12, 2021 10:10 am
Location: Russian Totalitarian Republic, Perm city

Re: Copy Range frome book 2 book Excel

Post by toopnad » Mon Aug 02, 2021 5:05 pm

I open the file this way, but for using XLRunCode I need a handle variable or am I wrong?

Code: Select all

VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename,,False,,,123)
end sub
VBEND
 VBRun>OpenExcelFile,Z:\Обмен\Поставка\Цены+Учет\УЧЕТ\Учет движения поставок ПЗПК роботизированный.xlsx

User avatar
Grovkillen
Automation Wizard
Posts: 1009
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: Copy Range frome book 2 book Excel

Post by Grovkillen » Mon Aug 02, 2021 5:20 pm

Instead if using a VBScript chunk use the XLOpen and inject the VBA using the XLRunCode command.
Let>ME=%Script%

Running: 15.0.24
version history

toopnad
Junior Coder
Posts: 34
Joined: Mon Apr 12, 2021 10:10 am
Location: Russian Totalitarian Republic, Perm city

Re: Copy Range frome book 2 book Excel

Post by toopnad » Tue Aug 03, 2021 3:49 am

As I said, the file contains a password for editing. XLO doesn't work i tried adding password to command record ! Therefore it is impossible. I cannot use commands without Handle Variable. Maybe Im stupid? :oops:
O! I think I can use XLC in the second file that I open with XLO. Let's try!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Copy Range frome book 2 book Excel

Post by Dorian (MJT support) » Tue Aug 03, 2021 10:10 am

Grovkillen is correct, this will open a password protected WorkBook - e.g. open the file.

Code: Select all

//XLopen>d:\pwtest.xlsx,1,xlh,test
However, I think you mean the sheet is protected, not the file. I've never needed to do this before so I experimented a little, and my knowledge of VBA is almost zero. I was able to use VBA snippets found online to unprotect/edit/protect a protected worksheet.

Code: Select all

XLopen>d:\pwtest.xlsx,1,xlh

//Unprotect
LabelToVar>Unprotect,UnprotectVBA
XLRunCode>xlh,UnprotectVBA

//Write
XLSetCell>xlh,Sheet1,1,1,I am unprotected,

//Protect
LabelToVar>Protect,ProtectVBA
XLRunCode>xlh,ProtectVBA

/*
Unprotect:
    Sheets("Sheet1").Select
    ActiveSheet.Unprotect (Password = "zzz")
*/

/*
Protect:
    Sheets("Sheet1").Select
    ActiveSheet.Protect (Password = "zzz")

*/
Yes, we have a Custom Scripting Service. Message me or go here

toopnad
Junior Coder
Posts: 34
Joined: Mon Apr 12, 2021 10:10 am
Location: Russian Totalitarian Republic, Perm city

Re: Copy Range frome book 2 book Excel

Post by toopnad » Tue Aug 03, 2021 12:15 pm

The file is protected. Such an entry is not suitable because the password protects not opening, but editing! There are two kinds of password Password and WriteResPassword!
I have already worked around the problem via VBA in another file, but the error appears. Everything works, but the error stops the process. Let's find the reason.
CopyRange:
Sheets("заявка в поставку").Select
lr = Cells(Rows.Count, 4).End(xlUp).Row
Range(Cells(8, 4), Cells(lr, 15)).Select
Selection.Copy
file = "Z:\Обмен\Поставка\Цены+Учет\УЧЕТ\Учет движения поставок ПЗПК роботизированный.xlsx"
Set importWb = Workbooks.Open(file, , False, , , 123)
lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Долги").Select
Cells(lRow, 1).PasteSpecial Paste:=xlPasteValues
importWb.Save
importWb.Close
path="Z:\Обмен\ЗАЯВКИ\Архив\ЗАЯВКИ в поставку\Отеч\2021\"
ThisWorkbook.SaveAs path & [C4] & "_" & [C5] & "_" & [C3] & ".xlsb"
Application.Quit
The code works completely without a macro and it is also completely executed in the macro, what is the reason for the error? I cannot finish the script.
ImageImage

Code: Select all

//Set IGNORESPACES to 1 to force script interpreter to ignore spaces.
//If using IGNORESPACES quote strings in {" ... "}
Let>IGNORESPACES=1
//Let>tmp0=Z:\Рассылка\Ответы\*.xlsx
//IfFileExists>tmp0
//Goto>startMacro
//else
//MessageModal>Программа успешно завершена!
//EndIf
//Label>startMacro
VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename,,False,,,123)
  LastRow = xlBook.Sheets("Sheet2").Cells(1, 1).End(xlUp).Row + 1
  Range(LastRow,1).paste
end sub
VBEND
Let>GFL_TYPE=0
Let>GFL_SORTTYPE=1
IfFileExists>Z:\Обмен\ЗАЯВКИ\ЗАЯВКИ в поставку\ОТЕЧ\2021\*.*
GetFileList>Z:\Обмен\ЗАЯВКИ\ЗАЯВКИ в поставку\ОТЕЧ\2021\*.*,files,;
Separate>files,;,file_names
MessageModal>Num Files: %file_names_count%
Let>k=0
Repeat>k
  Let>k=k+1
  XLO>file_names_%k%,1,xlh
  //Message>file_names_%k%2
  //wait>15
    //Extract the file name from the entire path/filename
  ExtractFileName>file_names_%k%,oldfilename,1
  Let>WF_TYPE=2
  WaitWindowOpen>%oldfilename% - Excel
  Let>regPath=Z:\Обмен\ЗАЯВКИ\Архив\Реестр\Реестр отеч.xlsx
  XLOpen>regPath,1,xlBook
  Let>WF_TYPE=2
  WaitWindowOpen>Реестр отеч - Excel
    Let>k1=0
    Label>scan
    Let>k1=k1+1
    DDERequest>Excel,regPath,R%k1%C1,result,0
    If>result<>CRLF,scan
      //So k is first empty cell, put clipboard contents in it
      DDERequest>Excel,file_names_%k%,R2C3,firm,0
      DDERequest>Excel,file_names_%k%,R3C3,date,0
      DDERequest>Excel,file_names_%k%,R5C3,contragent,0
      let>k2=k1-1
      let>lastNum="=R[-1]C3+1"
      //DDERequest>Excel,regPath,R%k2%C3,lastNum,0
      DDEPoke>Excel,regPath,R%k1%C1,firm
      DDEPoke>Excel,regPath,R%k1%C2,date
      DDEPoke>Excel,regPath,R%k1%C3,lastNum
      DDEPoke>Excel,regPath,R%k1%C4,contragent
      DDERequest>Excel,regPath,R%k1%C3,num,0
      DDEPoke>Excel,file_names_%k%,R4C3,num
      XLSave>xlBook,regPath
      XLQuit>xlBook
      Let>WF_TYPE=2
      SetFocus>%oldfilename% - Excel
//Read Copy/paste VBA
LabelToVar>CopyRange,CopyCode
//Execue Copy/Paste VBA
XLRunCode>xlh,CopyCode
DeleteFile>file_names_%k%
      Until>k,file_names_count
Message>F[[eeeee!!!!
wait>40
else
//Отправить письмо что нет заявок
EndIf

/*
CopyRange:
Sheets("заявка в поставку").Select
lr = Cells(Rows.Count, 4).End(xlUp).Row
Range(Cells(8, 4), Cells(lr, 15)).Select
Selection.Copy
file = "Z:\Обмен\Поставка\Цены+Учет\УЧЕТ\Учет движения поставок ПЗПК роботизированный.xlsx"
Set importWb = Workbooks.Open(file, , False, , , 123)
lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Долги").Select
Cells(lRow, 1).PasteSpecial Paste:=xlPasteValues
importWb.Save
importWb.Close
path="Z:\Обмен\ЗАЯВКИ\Архив\ЗАЯВКИ в поставку\Отеч\2021\"
ThisWorkbook.SaveAs path & [C4] & "_" & [C5] & "_" & [C3] & ".xlsb"
Application.Quit
*/

toopnad
Junior Coder
Posts: 34
Joined: Mon Apr 12, 2021 10:10 am
Location: Russian Totalitarian Republic, Perm city

Re: Copy Range frome book 2 book Excel

Post by toopnad » Tue Aug 03, 2021 1:51 pm

Checked the code line by line error appears when closing the second file. And it doesn't matter what method is used.
Application.Quit or ThisWorkbook.Close

User avatar
Grovkillen
Automation Wizard
Posts: 1009
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: Copy Range frome book 2 book Excel

Post by Grovkillen » Tue Aug 03, 2021 2:48 pm

Well, I think it's pretty obvious. You're closing the document within Excel, this will of course make MacroScheduler become angry. Leave it out and close it with the XLQuit.
Let>ME=%Script%

Running: 15.0.24
version history

toopnad
Junior Coder
Posts: 34
Joined: Mon Apr 12, 2021 10:10 am
Location: Russian Totalitarian Republic, Perm city

Re: Copy Range frome book 2 book Excel

Post by toopnad » Tue Aug 03, 2021 3:32 pm

Exactly! I think you are absolutely right! I try to save, close, and then rename and transfer the file to a new folder. Thank You Very Much!
PS It really helped!

Post Reply
cron
Sign up to our newsletter for free automation tips, tricks & discounts