Sign in to follow this  
Followers 0
Gumma

Reading Excel and Whitespace

1 post in this topic

So I had a question about the best way to solve this problem. I'm writing data to an excel sheet. This data writing only needs to be one way... I don't need to edit it through the program at all. My problem comes from formatting. Right now the variable $QSID is a string of numbers, often with one or two leading zeros. By default, excel drops these. However, if you change the cell attribute to "text" instead of numbers, it will allow them. So, thinking I could just go ahead and do that, I opened my chart and changed all the cells to text. Well... after I did that, when I use ReadSheetToArray, it returns those formatted cells as containing data... so when I use _ExcelWriteArray($oExcel, $row, 1, $aRxData), it will just write the data to the last cell that I have not formated (maybe like... row 4000). I'm guessing I need to determine which $row to write in using a different method. I saw a function for removing white space, but I'm not sure if that would be helpful here. I've also read about using macros, but I'm not sure how I'd impliment them.

Any suggestions would be greatly apperciated.

;start excel stuff
$sFilePath1 = "file.xls" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1, 0); 

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist!")
    Exit
EndIf

$aArray = _ExcelReadSheetToArray($oExcel)
$row = $aArray[0][0] + 1 ; used to detect the next open collumn.
$col = $aArray[0][1]

Local $aRxData[5] = [$QSID, $Date, $Prescription, " ", $User]

_ExcelWriteArray($oExcel, $row, 1, $aRxData) ; write array after existing data
sleep(500)
_ExcelBookSaveAs($oExcel, "file.xls", "xls", 0, 1) ; overwrite existing file if necessary
_ExcelBookClose($oExcel) ; 


EndSwitch
WEnd

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  
Followers 0