Jump to content
Sign in to follow this  
smellyfingers

Excel, weirdness

Recommended Posts

smellyfingers

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

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
smellyfingers

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 :)

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
smellyfingers

I'm not sure how to write this array that i get from fileread, from the examples that are in the helpfile the array is first declared, so how do i put an existing array i get from fileread to write the array into excel, when i try to write the array nothing happends :/

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
smellyfingers

Awsome water!

I really appreachiate your help, can't test this until tomorrow tho :/ but i will post the result when i have, Thanx again :)

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
smellyfingers

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 :)

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
smellyfingers

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

Share this post


Link to post
Share on other sites
water

:D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
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
Sign in to follow this  

×