Insert Into EXCEL
Hi All,
i understand the concept of exporting information from an Excel spreadsheet cell into a textbox
Text1.Text = xlsheet.Cells(2, 3).Text
but how would i get my app to insert into excel from a textbox in vb6
any help would be apprecited
many thankx in advance
Rob
[317 byte] By [
Spumbu1977] at [2007-11-11 8:49:15]
# 1 Re: Insert Into EXCEL
Just turn it around:
xlsheet.Cells(2, 3).Text = Text1.Text
# 2 Re: Insert Into EXCEL
hey Phil,
thanks for your quick reply, everytime i use the .text it tells me
unable to set the Text property of the Range Class
is that perhaps not a format erro
even though it is a blank spreadhseet been made form scratch
thanks
# 3 Re: Insert Into EXCEL
Try Value instead of Text (for the Cell range).
# 4 Re: Insert Into EXCEL
Hi , i tried .value and it does not enter anything into the excel spreadsheet
thanks
my code is as follows
Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook
Set xlwbook = xl.Workbooks.Open("c:\stats2006\docs\test.xls")
Set xlsheet = xlwbook.Sheets.Item(2)
xlsheet.Cells(2, 4).Value = Text7.Text
xl.ActiveWorkbook.Close False, "c:\stats2006\docs\stat.xls"
xl.Quit
# 5 Re: Insert Into EXCEL
I don't understand your code. You open a Workbook called test.xls and set cell (2,4) on the second sheet to the value of your TextBox. Then you close the active Workbook and specify stat.xls but don't save any changes and then quit Excel. Is this what you want to do?
From this code you're not saving any changes.
# 6 Re: Insert Into EXCEL
hi,
yes sorry i was just testing the excel import fro vb6 part ,
the last line in my code actually reads
xl.ActiveWorkbook.Close False, "c:\stats2006\docs\test.xls"
i actually want to open the excel spreadsheet point at the cell i wish to have the text enterd into and then close the workbook and then close the excel connection
thanks
# 7 Re: Insert Into EXCEL
Hi,
perhaps this is easier:
Dim xlExcel As New Excel.Application
xlExcel.Workbooks.Open "c:\stats2006\docs\test.xls"
xlExcel.ActiveWorkbook.Sheets(2).Cells(2, 4).Value = Text7.Text
xlExcel.ActiveWorkbook.SaveAs "c:\stats2006\docs\stat.xls"
xlExcel.ActiveWorkbook.Close
xlExcel.Quit
Set xlExcel = Nothing
# 8 Re: Insert Into EXCEL
hi mate,
the above code works great but is there anyway that i can get it to save changes wihout prompting me for a responce
right now it gives me three options
yes no and cancel
thanks in advance
# 9 Re: Insert Into EXCEL
Set the DisplayAlerts property of the Application object to False, save and close the Workbook, and then set it back to True just before you quit Excel.
# 10 Re: Insert Into EXCEL
How exactly do you change the DisplayAlerts property? I cant seem to find this option anywhere.
Force at 2007-11-11 17:34:14 >
# 11 Re: Insert Into EXCEL
xlExcel.Application.DisplayAlerts = False
# 12 Re: Insert Into EXCEL
Thanks that fixed my error, can you tell me what is wrong with this portion of code it seemed to be working earliar but no longer:
xlexcel.range(a1).Value = txt_acct.Text
Force at 2007-11-11 17:36:12 >
# 13 Re: Insert Into EXCEL
xlexcel.activesheet.range("a1").Value = txt_acct.Text
This should be better, let me know if it doesn't work. (Post also the error message then)
# 14 Re: Insert Into EXCEL
did a small edit in the previous post
# 15 Re: Insert Into EXCEL
Sweet, Thanks man I appreciate it I guess the "activesheet" part is required.
Force at 2007-11-11 17:39:16 >
# 16 Re: Insert Into EXCEL
No problem, glad I could help
# 17 Re: Insert Into EXCEL
Ok heres another question for you is there anyway to assign a Integer for the range?
When I try to run this code:
xlExcel.activesheet.range("a,intvalue").Value = txt_acct.Text
I get the error "Run Time Error 1004:" "application defined or object defined error"
Sorry for all the questions I am still very new to this.
** I found it can be done once the integers were converted to strings **
Force at 2007-11-11 17:41:18 >
# 18 Re: Insert Into EXCEL
DisplayAlerts is a method of the Application object. I'm assuming that Benjamin's post is referencing a Document object first then the Application object.
# 19 Re: Insert Into EXCEL
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last column with data in it (must also copy the top para for this to work)
LastColWithData = ExcelLastCell.Column
Col = ExcelLastCell.Column
Do While Application.CountA(ActiveSheet.Columns(Col)) = 0 And Col <> 1
Col = Col - 1
Loop
LastColWithData = Col ' Column number
I found this code to find the last cell in a column that has data in it. But when I run the code it says: Object Required and highlights the Set command. Any ideas?
Force at 2007-11-11 17:43:17 >
# 20 Re: Insert Into EXCEL
It probably doesn't know what ActiveSheet is associated with. Try specifying the Workbook object as well.
# 21 Re: Insert Into EXCEL
hi, i am trying to join two differents project into one project. if i run the first and the second project, the project works well. but if i copy-paste the form in the second one into the first, the new project wouldn't work...
thx for the attention
