add value to existing Excel file 1

[davyre]

Hi,

I want to create a button in a form to 'edit' an existing Excel file, that is to add something (a variable's value from a table in Access) to a certain cell in the Excel file. I do not want the VBA to overwrite the Excel file because it will scrape all the existing data from the excel, but rather just want to add some data to the Excel file. Can anyone help me how?
note: I already have a way to extract the directory from the excel file.

So basically this is the steps what I want :
a button is clicked (OK)
a dialog window is opened to specify the directory of the existing excel file (OK)
vba code updates/adds data to the excel file ---> need help
save excel file
Done!

lets say the data I want to add is

Dim CustName as string
Dim directory as string
Dim strFilter As String
Dim StrInputFileName As String

Dim objExcel As Object
Dim objBook As Object
Dim objSheet As Object

strFilter = ahtAddFilterItem(myStrFilter, "Excel Files (*.xlsx)", "*.xlsx")
strSaveFileName = ahtCommonFileOpenSave( _
                                OpenFile:=False, _
                                Filename:=rst!vendor, _
                                filter:=strFilter, _
                                Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

directory=strSaveFileName 'the value of the variable will be like C:\User\Desktop\A.xlsx

Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.add
Set objSheet = objExcel.Worksheets("Sheet1")
objBook.SaveAs (strSaveFileName) 'I suspect of this caused the VBA to overwrite the excel file

CustName=dlookup("CustName","TblCustomer","CustID=" & Me.CustomerBox.Value)

objSheet.Range("A2").Value=CustName

objBook.Save

 

[PHV]

Replace this:
Set objBook = objExcel.Workbooks.add
with this:
Set objBook = objExcel.Workbooks.Open(strSaveFileName)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Also your code will assign a CustName to A2 EVERY TIME, overwriting what is already there. What logic do you want to apply in order that that will not happen?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davyre]

Skip, I am intentionally placed CustName to A2 because the Excel file is like a template, where A1 is the title and A2 should contain the customer name. Thanks!

 

[davyre]

now that I have succeed to use excel as template and save, how can I do this:

1. A button is clicked (OK)
2. A dialog window is opened to specify the directory of the existing excel file (OK)
3. vba code updates/adds data to the excel file ---> (OK)
4. save excel file to A NEW EXCEL FILE (i.e if the first excel file named Unit1Tpl.xls, then the new Excel file will be named Unit1Tpl001.xls, Unit1Tpl002, etc etc) --->need help!
Done!

so the previous excel file (Unit1Temp.xls) is not edited, rather a new excel file which is a copy of Unit1Temp.xls with added data will be created. Any help on this? Thanks