saving user input to existing excel

[evo88215]

Hi, I'm in need of some of help with saving user input data to specific cells without having to making a code for every cell.

IE, user inputs 4 data and they need to be stored in a specific formatted excel sheet. I can't think of a shorter way of doing this instead of duplicating multiple codes.

Thanks

[FanaticGuru]

Hi, I'm in need of some of help with saving user input data to specific cells without having to making a code for every cell.

IE, user inputs 4 data and they need to be stored in a specific formatted excel sheet. I can't think of a shorter way of doing this instead of duplicating multiple codes.

Thanks

You did not provide much information but maybe this is a shot in the right direction:

Code: Select all

CellLocs := {}
CellLocs.Push("A1")
CellLocs.Push("B3")
CellLocs.Push("B5")
CellLocs.Push("C4")

InputBox, Data, Enter Data for Excel, Enter Four Data Item`nEach Separated by a Space: 

xlApp := ComObjActive("Excel.Application")

for Index, Val in StrSplit(Data, " ")
  xlApp.ActiveSheet.Range(CellLocs[Index]).Value := Val

FG

[evo88215]

Hi, I'm in need of some of help with saving user input data to specific cells without having to making a code for every cell.

IE, user inputs 4 data and they need to be stored in a specific formatted excel sheet. I can't think of a shorter way of doing this instead of duplicating multiple codes.

Thanks

You did not provide much information but maybe this is a shot in the right direction:

Code: Select all

CellLocs := {}
CellLocs.Push("A1")
CellLocs.Push("B3")
CellLocs.Push("B5")
CellLocs.Push("C4")

InputBox, Data, Enter Data for Excel, Enter Four Data Item`nEach Separated by a Space: 

xlApp := ComObjActive("Excel.Application")

for Index, Val in StrSplit(Data, " ")
  xlApp.ActiveSheet.Range(CellLocs[Index]).Value := Val

FG

hi sorry, so this is what I was trying to accomplish.

but I want to shorten the code as much as possible, as there will be probably over 30-40 data entries. I just want the data entered in the correct range on excel and in the format it was created in(chronologically)

also noted, for example i have a set column for A in excel. and 30-40 rows each labeled same for top 2 row as header. so the cell data range would be from B3:D36.

so when a user inputs say 20 as the data/row.. the following user inputs will be in that row but either B C or D. hopefully I explained myself better

here's the format I want the user inputs to be sent to

Code: Select all

FilePath := "C:\Users\Sunny\Desktop\test.xlsx"
oExcel := ComObjCreate("Excel.Application")
oExcel.DisplayAlerts := False
oWorkBook := oExcel.Workbooks.Open(FilePath)
oExcel.Visible := False

InputBox, 1,, Enter data, nohide, 200, 130
Sleep, 500
InputBox, 2,, Enter 2nd data, nohide, 200, 130

		oExcel.Range("A4").Value := 1
		oExcel.Range("B4").Value := 1

oWorkBook.Save()
oWorkbook.Close()

[evo88215]

bump, so I've gotten help and decided to make a GUI, but now using the GUI it's not writing to correct cell path when an image is found.

Code: Select all

F1::

Gui, New
Gui, Font, s11, Calibri            
Gui, Add, Text, , Password:
Gui, Add, Text, , Eng:            
Gui, Add, Text, , Heater:
Gui, Add, Text, , Air:
Gui, Add, Edit, w100 y5	vpassword Password
Gui, Add, Edit, w100 	veng,
Gui, Add, Edit, w100    vheat,
Gui, Add, Edit, w100    vair,
Gui, Add, Button, gButtonOK Default, OK    
Gui, Show,, EQSO ver. 4
Return

ButtonOK:
Gui, Submit, NoHide

FilePath := "C:\Users\Desktop\test.xlsx"
oExcel := ComObjCreate("Excel.Application")
oExcel.DisplayAlerts := False
oWorkBook := oExcel.Workbooks.Open(FilePath)
oExcel.Visible := False

		
			ImageSearch, x, y, 0, 0, A_ScreenWidth, A_ScreenHeight, %A_ScriptDir%\605.png
			if ErrorLevel {
				ImageSearch, x, y, 0, 0, A_ScreenWidth, A_ScreenHeight, %A_ScriptDir%\609.png
				oExcel.Range("B30").Value := eng
				oExcel.Range("C30").Value := heat
				oExcel.Range("D30").Value := air
				}
					else if ErrorLevel	{
					ImageSearch, x, y, 0, 0, A_ScreenWidth, A_ScreenHeight, %A_ScriptDir%\606.png
					oExcel.Range("B31").Value := eng
					oExcel.Range("C31").Value := heat
					oExcel.Range("D31").Value := air
					}
						else if ErrorLevel	{
						ImageSearch, x, y, 0, 0, A_ScreenWidth, A_ScreenHeight, %A_ScriptDir%\607.png
						oExcel.Range("B35").Value := eng
						oExcel.Range("C35").Value := heat
						oExcel.Range("D35").Value := air
						}
							else	{
							oExcel.Range("B36").Value := eng
							oExcel.Range("C36").Value := heat
							oExcel.Range("D36").Value := air
							}
							
							oExcel.Save()
							oExcel.Quit()

[FanaticGuru]

bump, so I've gotten help and decided to make a GUI, but now using the GUI it's not writing to correct cell path when an image is found.

Code: Select all

F1::

Gui, New
Gui, Font, s11, Calibri            
Gui, Add, Text, , Password:
Gui, Add, Text, , Eng:            
Gui, Add, Text, , Heater:
Gui, Add, Text, , Air:
Gui, Add, Edit, w100 y5	vpassword Password
Gui, Add, Edit, w100 	veng,
Gui, Add, Edit, w100    vheat,
Gui, Add, Edit, w100    vair,
Gui, Add, Button, gButtonOK Default, OK    
Gui, Show,, EQSO ver. 4
Return

ButtonOK:
Gui, Submit, NoHide

FilePath := "C:\Users\Desktop\test.xlsx"
oExcel := ComObjCreate("Excel.Application")
oExcel.DisplayAlerts := False
oWorkBook := oExcel.Workbooks.Open(FilePath)
oExcel.Visible := False

		
			ImageSearch, x, y, 0, 0, A_ScreenWidth, A_ScreenHeight, %A_ScriptDir%\605.png
			if ErrorLevel {
				ImageSearch, x, y, 0, 0, A_ScreenWidth, A_ScreenHeight, %A_ScriptDir%\609.png
				oExcel.Range("B30").Value := eng
				oExcel.Range("C30").Value := heat
				oExcel.Range("D30").Value := air
				}
					else if ErrorLevel	{
					ImageSearch, x, y, 0, 0, A_ScreenWidth, A_ScreenHeight, %A_ScriptDir%\606.png
					oExcel.Range("B31").Value := eng
					oExcel.Range("C31").Value := heat
					oExcel.Range("D31").Value := air
					}
						else if ErrorLevel	{
						ImageSearch, x, y, 0, 0, A_ScreenWidth, A_ScreenHeight, %A_ScriptDir%\607.png
						oExcel.Range("B35").Value := eng
						oExcel.Range("C35").Value := heat
						oExcel.Range("D35").Value := air
						}
							else	{
							oExcel.Range("B36").Value := eng
							oExcel.Range("C36").Value := heat
							oExcel.Range("D36").Value := air
							}
							
							oExcel.Save()
							oExcel.Quit()

Since you have a handle to the Workbook, oWorkBook := oExcel.Workbooks.Open(FilePath), I would use that.

oWorkBook.Range("B30").Value := eng
I don't know if that is the problem but it is better practice in general.

Also, not a fan of your If-else structure. I doubt it is doing what you expect.

FG