With the help of this site I developed this code to append a query to an existing spreadsheet. The problem I am having is Row 1:1 is a label row in the Excel spreadsheet so I need this data to start a Row 2:2. I tried changing to .Range("A" & i + 1).Value after the i but that didn't do the trick. Can someone identify my problem?
Code:
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBisconer.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11152", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i).Value = rs.Fields("GLCode").Value
.Range("C" & i).Value = rs.Fields("LOC").Value
.Range("D" & i).Value = rs.Fields("Customer").Value
.Range("E" & i).Value = rs.Fields("LastName").Value
.Range("F" & i).Value = rs.Fields("FirstName").Value
.Range("G" & i).Value = rs.Fields("CardNo").Value
.Range("H" & i).Value = rs.Fields("Date").Value
.Range("I" & i).Value = rs.Fields("BusinessType").Value
.Range("J" & i).Value = rs.Fields("Commodity").Value
.Range("K" & i).Value = rs.Fields("SupplierName").Value
.Range("L" & i).Value = rs.Fields("Amount").Value
.Range("M" & i).Value = rs.Fields("Department").Value
End With
i = i + 1
rs.MoveNext
Loop
rs.Close
db.Close
Exit Sub
ErrorHandler:
' Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
' Resume with statement following occurrence of error.
Resume Next
End Sub
I tried that and it didn't seem to work either so I went back to the spreadsheet I was appending to and found when I removed this WorkbookOpen() code it worked fine. I believe now my issue is with Excel not Access.
Sub FormatBisconer()
Sheets(1).Select
'Create LookupBoxs to column A:A
Range("A2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=GLExpense"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("A2").Select
Selection.Copy
Range("A3:A" & Range("H65536").End(xlUp).Row).Select
ActiveSheet.Paste
Range("A2").Select
'Add Vlookup for the GLCode in Column B:B
Sheets(1).Range("B2").Formula = "=IF(ISNA(VLOOKUP(A2,GlCode,2)),0,VLOOKUP(A2,GlCode,2))"
Sheets(1).Range("C2").Formula = "=VLOOKUP(G2,Department,2)"
Range("B2:C2").Select
Selection.Copy
Range("B3:C" & Range("H65536").End(xlUp).Row).Select
ActiveSheet.Paste
Range("A2").Select
'Add Vlookup to populate the Department Column L:L
Sheets(1).Range("M2").Formula = "=VLOOKUP(G2,Department,3)"
Range("M2").Select
Selection.Copy
Range("M2:M" & Range("F65536").End(xlUp).Row).Select
ActiveSheet.Paste
Range("A2").Select
Dim X As Integer
For X = 2 To Range("I" & Rows.Count).End(xlUp).Row
'Predetermined GLExpense field based on commodity
If UCase(Range("J" & X).Value) = "FUEL" And UCase(Range("M" & X).Value) = "ADMIN" Then
Range("A" & X).Formula = "ADM-VEHICLE FUEL"
ElseIf UCase(Range("J" & X).Value) = "AIRLINES" And UCase(Range("M" & X).Value) = "ADMIN" Then
Range("A" & X).Formula = "SLS-TRAVEL/PUB TRANS/LODG"
ElseIf UCase(Range("J" & X).Value) = "RESTAURANTS & EATERIES" And UCase(Range("M" & X).Value) = "ADMIN" Then
Range("A" & X).Formula = "SLS-ENTERTAINMENT"
ElseIf UCase(Range("J" & X).Value) = "COMPUTER SUPPLIES" And UCase(Range("M" & X).Value) = "ADMIN" Then
Range("A" & X).Formula = "ADM-OFFICE SUPPLIES"
ElseIf UCase(Range("J" & X).Value) = "OFFICE SUPPLIES" And UCase(Range("M" & X).Value) = "ADMIN" Then
Range("A" & X).Formula = "ADM-OFFICE SUPPLIES"
ElseIf UCase(Range("J" & X).Value) = "POSTAL SERVICES" And UCase(Range("M" & X).Value) = "ADMIN" Then
Range("A" & X).Formula = "ADM-OFFICE SUPPLIES"
ElseIf UCase(Range("J" & X).Value) = "FUEL" And UCase(Range("M" & X).Value) = "SALES" Then
Range("A" & X).Formula = "SLS-VEHICLE FUEL"
ElseIf UCase(Range("J" & X).Value) = "FUEL" And UCase(Range("M" & X).Value) = "OFFICE" Then
Range("A" & X).Formula = "ADM-VEHICLE FUEL"
ElseIf UCase(Range("J" & X).Value) = "RESTAURANTS & EATERIES" And UCase(Range("M" & X).Value) = "SALES" Then
Range("A" & X).Formula = "SLS-ENTERTAINMENT"
