CODEWITHSUNDEEP
excelautohotkey
Gilbert V
Gilbert V

Reputation: 1040

AutoHotKey Writing into a existing excel file, and remove save prompt

No I am trying to have my test code fill up the information I web scraped into a excel file. But I seem to be getting a illegal character error all over some of the data. And when I tried to just add some random data to save it into the excel file it gives me a prompt asking me to save it. What would be the best way of removing or filtering out these illegal characters ? If I have to use StrReplace that is fine but I want to know If that is the best way. and how do I pass variables into the excel file, even without a error I seem to have a problem passing %variables% into the cell is there another way of doing it ?

Here is the code below:

CloseExcel(){
oExcel := ComObjCreate("Excel.Application")
oExcel.DisplayAlerts := False ; This removes the prompt
FilePath := "C:\Users\GGVERA\Documents\aviability\test.csv" ; example path this works
cWorkbook := ComObjGet(FilePath) ; access Workbook object

oWorkbook.Sheets(1).Range("G2").Value := "TestData"
cWorkbook.Sheets(1).Range("H2").Value := %Name%         ; Name

oWorkbook.Save()
oWorkbook.Close()
}

Upvotes: 0

Views: 1732

Answers (1)

Jim U
Jim U

Reputation: 3366

This modifies a CSV file in Excel without prompting "Do you want to save the changes you made to 'myfile.csv'?"

CloseExcel(FilePath){

  oExcel := ComObjCreate("Excel.Application")
  oExcel.DisplayAlerts := False

  oWorkbook := ComObjGet(FilePath)
  oWorkbook.Sheets(1).Range("G2").Value := "TestData"
  oWorkbook.Save()
  oWorkbook.Close()
}

I suggest using RegExMatch() and RegExReplace() to extract and clean information from web pages you've scraped.

Upvotes: 1

Related Questions