Hello Gurus
I am not new in VBA, but just rookie in Access VBA. I got a problem with my code. I need to open several excel files created by other areas and update a database (a table call Customers that needs update and/or append the record if does not exist or update other records if exist).
The excel files have the following characteristics (see down here picture):
- The data that I need to update starts at a variable row, so in order to know where I need to look for the word “Rate”
- Once the word Rate is found, this row contains the name of the fields to be updated/appended
- There is an empty row after this row
- The quantity of rows that can be updated/appended is also variable
Column A Column B Column C Row 1 Row 2 ….. Row N
i.eA23Rate Customer Account # Premise # Empty row Row N+2 Lml22 Customer 1 1178954 54544 Row N+3 Lml5 Customer 2 1175489 1254
My Access Table has the same name fields that the excel files, however the field “Premise #” is the principal key and is indexed.
My code works until the use of DoCmd.TransferSpreadsheet, as it give an error message that the records were lost due to key violations so I do not know if I cannot use this Method.According to the metod, UpRange is a string that contains the data from the excel file in the example case is “A23:D408” Here is my code (includes a macro to enter the file from a directory that works - call selectFile not included):
Sub PopulateCorrectAdd()
Dim i As Long, j As Long, RowStart As Long, LastRow As Long
Dim Rw As Long
Dim UpRange As String, Rowloc As String
Dim xlApp As Object
Dim WorkBk As Object
'Dim sh As Excel.worksheet
Call SelectFile
'Open excel file and find first record to update--------------------------------------------
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
'Set WorkBk = appExcel.Workbooks.Open("FolderPathData")
xlApp.Workbooks.Open FolderPathData, True, False
Debug.Print xlApp.Version
Worksheets(1).Cells.Find(What:="Rate", after:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Rowloc = ActiveCell.Address
Rowloc = Mid(Rowloc, 4, 2)
RowStart = Val(Rowloc)
ActiveCell.Offset(2, 0).Select
LastRow = ActiveCell.CurrentRegion.Rows.Count + RowStart + 1
UpRange = "A" & RowStart & ":" & "D" & LastRow
'Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customers", _
FolderPathData, True, UpRange
'WorkBk.Close
Set xlApp = Nothing
xlApp.Quit
End Sub
thanks I appreciate any help