Full Version: Acess/Excel Question
UtterAccess Forums > Microsoft® Access > Access Forms
drmojo418
not for sure if i should post this in here or in the excel section. i have a form in my database that on a buttons on click event it creates a excel spreadsheet. my question is there a way that when the spreadsheet is created instead of over writing the data already created in my spreadsheet that i would be able to to just add to it.
Thanks
chad
fkegley
Instead of having the button create a spreadsheet, have it open the existing one. I can't tell you off the top of my head how to do it, but I know that it can be done. You can probably even reference range names in the spreadsheet. I know that you can reference individual cells.
drmojo418
could someone off the top of there head tell me how to do what fkegley is talking about.
Thanks
chad
drmojo418
bump
CitiTcc
Dim xlsApp As Excel.Application
Dim xlsWkb As Excel.Workbook
Dim i As Integer
Dim strcell As String
= 1
Set xlsApp = CreateObject("Excel.application")
xlsApp.Visible = True
Set xlsWkb = xlsApp.Application.Workbooks.Open("filepath here")
xlsApp.Sheets("sheet1").Range("a" & CStr(i)).Select 'Select cell A1
strcell = xlsWkb.Sheets("sheet1").Range("a" & CStr(i)).Select 'Sets value of strCell
Do Until strcell = "" 'loop until we find the firs cell with no value
xlsWkb.Sheets("sheet1").Range("a" & CStr(i)).Select ' selects cell being evaluated
strcell = xlsWkb.Sheets("sheet1").Range("a" & CStr(i)) ' assigns value to strCell
O= i + 1 'adds 1 to i to select next cell
Loop
rest of code here
be sure that the MS Excel reference is checked under Tools/Reference or Dim xlsApp and xlsWkb as Objects
HTH
drmojo418
This is what my code looks like:
unction SD_exportToExcel()
Dim xlsApp As Excel.Application
Dim xlsWkb As Excel.Workbook
Dim i As Integer
Dim strcell As String
O= 1
Set xlsApp = CreateObject("Excel.application")
xlsApp.Visible = True
Set xlsWkb = xlsApp.Application.Workbooks.Open("c:\my documents\sD_dailySales.xls")
xlsApp.Sheets("qrySD_Sales").Range("a" & CStr(i)).Select 'Select cell A1
strcell = xlsWkb.Sheets("qrySD_Sales").Range("a" & CStr(i)).Select 'Sets value of strCell
Do Until strcell = "" 'loop until we find the firs cell with no value
xlsWkb.Sheets("qrySD_Sales").Range("a" & CStr(i)).Select ' selects cell being evaluated
strcell = xlsWkb.Sheets("qrySD_Sales").Range("a" & CStr(i)) ' assigns value to strCell
i = i + 1 'adds 1 to i to select next cell
Loop
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySD_Sales", _
filename:="c:\my documents\sD_dailySales.xls", _
hasfieldnames:=True
DoCmd.SetWarnings False

End Function
it opens up excel but it doesn't update what i have open on my form. nor does it ask for the dates like it did before.
thanks
chad
drmojo418
bump2
drmojo418
bump3
drmojo418
Is there anyone out there that can help with this?
Thanks
chad
drmojo418
anyone at all, should this post be in the excel part?
Thanks
chad
fkegley
I see you putting data into strcell but I don't see any data being sent to the worksheet.
o send data to worksheet, cell reference must appear on left of =.
drmojo418
i don't follow you. what do you mean when you say call reference must apper on the left of =.
Could you give me an example.
thanks
chad
fkegley
This is what I am talking about:
lsApp.Sheets("sheet1").Range("a" & CStr(i)).Select = 10
Assigning a value to strCell is all well and good but that value will not be transferred back to the worksheet until you put a cell reference on the left side of an = as I have
fkegley
OOPS Ignore my previous post it should have been:
xlsApp.Sheets("sheet1").Range("a" & CStr(i)).Value = 10
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.