Jump to content

Excel, weirdness


Recommended Posts

Hi, i'm having a lil problem with this code, the strange part is that i have 2 identical computer on the same network with the same excel version and settings(i think the settings are the same as i can see) but on 1 computer this string doesn't work:

$oExcel.Range("A" & $rownr & ":A" & $rownr).Select
$oExcel.Range("A" & $rownr & ":A" & $rownr).Activate

everything else works just this part, any suggestions what may be the cause, the file is in a network location and it seems that it takes a little bit longer for the other computer to open the file the first time, lag issue? I can see that excel makes a flash when it's suppose to activate the cell but it doesn't get activated

/Thanx

Link to comment
Share on other sites

Can you describe what you try to do whith your script?

I'm about to rewrite the Excel UDF and maybe there is already a function available.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

What it does is, it reads a line in a file to clipboard and is suppose to just paste into the excel so it ends up the same way as in the file, then the file gets deleted and a new file is downloaded, and back again until there is no more files, maybe it's better to put the content into an array and write the array instead, i'm just curios why it works on one computer and not the other :)

Link to comment
Share on other sites

Using _FileReadToArray and then _ExcelWriteSheetFromArray will be much faster.

If it works on one machine and doesn't on another then this can only be caused by different variable values ($rownr).

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

You need something like this. It takes all records from file @ScriptDir & "Test_Excel.txt" and writes it to the Excel worksheet:

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>

Global $aRecords1
; Read all lines into an array
_FileReadToArray(@ScriptDir & "\Test_Excel.txt", $aRecords1)
; Create a 2D array out of the 1D array
Global $aRecords2[$aRecords1[0]][1]
For $i = 1 To  $aRecords1[0]
    $aRecords2[$i-1][0] = $aRecords1[$i]
Next
; Create a new workbook and write the records to the worksheet
$oExcel = _ExcelBookNew()
_ExcelWriteSheetFromArray($oExcel, $aRecords2, 1, 1, 0, 0)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

If you like you could try the latest Beta version of my rewrite of the Excel UDF. You could then drop the step to create a 2D array of the 1D array returned by _FileReadToArray.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Hi again, this function is very fast but i'm missing spaces and tabs and it writes everything in first and second cell in column 1 as single lines, that's why i used paste in the first place cause all the fields then get populated in the right spot instantly if i select the first cell, is there a way to include the tabs and spaces?

I'm going to try arraysort and see if that works, thanx water :)

Link to comment
Share on other sites

Can you please post a small example of the file so we have something to test with?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I solved it, thanx to you :), this works columnbased instead of rows wich is better for me

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>
Global $column
Global $aRecords1
; Read all lines into an array
_FileReadToArray(@ScriptDir & "\smurf.tsv", $aRecords1)
$Splitit = StringSplit($aRecords1[2], @TAB)
; Create a 2D array out of the 1D array
Global $aRecords2[$Splitit[0]][1]
For $i = 1 To  $Splitit[0]
     $aRecords2[$i-1][0] = $Splitit[$i]
 Next
$column = 1
; Create a new workbook and write the records to the worksheet
$oExcel = _ExcelBookNew()
 For $i = 1 To 10
_ExcelWriteSheetFromArray($oExcel, $aRecords2, 1, $column, 0, 0)
$column = $column + 1
Next

The for loop in the end was just put there to test the writing

Thanx for your help as always

Edited by smellyfingers
Link to comment
Share on other sites

:D

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...