CODEWITHSUNDEEP
excelvbams-access
Akshay Chari
Akshay Chari

Reputation: 43

Macro to transfer data from Access to Excel I am facing problem

I am new to excel macro access I have created a macro in access database that transfer data/query from MS Access database to excel. My code/macro was working fine till demo. But now I encounter a problem that is after the data is transferred to excel then value in column B should change the values for which I have updated conditions within for loop as below... Now it debugs at the first value FXV, in excel it does not changes the value to FFJ as I coded. However, when it is FLB it used to change the value properly but now it does not for FXV and macro becomes slow. can someone help in fixing this?

Here is where it debugs

Below is my complete code: - So macro transfer data from access to excel then in excel it should change the values present in column B which I have coded within FOR loop it was working but now it is not I don know why? can any1 help me on this please..... The problem is in For loop when I change the range i = 2 to 999 it will run and paste the data but it does not change the value of FXV to FFJ... if I change the range for i = 1 to 999 or for i = 0 to 999 it will debug as shown below.... please help....

Option Compare Database
Option Explicit

Private Sub Command0_Click()
ExportToExcel
End Sub

Public Sub ExportToExcel()

Dim xl As Excel.Application
Dim wbtarget As Workbook

Dim FinalExp As QueryDef
Dim rsFinalExp As Recordset

Dim i As Integer


'Set up reference to the query to export

Set FinalExp = CurrentDb.QueryDefs("FinalExp")

'Debug.Print FinalExp.SQL

'Set up the parameter
'Execute the query

Set rsFinalExp = FinalExp.OpenRecordset()

'programetically reference exel

Set xl = CreateObject("Excel.Application")

'set ref to the export workbook (Paste the Excel Path)

Set wbtarget = xl.Workbooks.Open("path.xlsm")

'use paste from recordset to put in excel sheet
wbtarget.Worksheets("SampleFile").Cells(2, 1).CopyFromRecordset rsFinalExp


'Replace the FWD_Code's in column 2
 For i = 0 To 9999

If Worksheets("SampleFile").Range("B" & i).Value = "FXV" Then
      Worksheets("SampleFile").Range("B" & i).Value = "FFJ"
 
   ElseIf Worksheets("SampleFile").Range("B" & i).Value = "FAM" Then
            Worksheets("SampleFile").Range("B" & i).Value = "FST"
 
   ElseIf Worksheets("SampleFile").Range("B" & i).Value = "FLB" Then
            Worksheets("SampleFile").Range("B" & i).Value = "FST"
            
 End If
 
 Next


'save workbook
wbtarget.Save

wbtarget.Close

Set wbtarget = Nothing
Set xl = Nothing

Set FinalExp = Nothing

End Sub

Upvotes: 0

Views: 216

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

You'd be better off declaring a worksheet variable, and using Select Case

Dim ws As Excel.Worksheet
Set wbtarget = xl.Workbooks.Open("C:\path.xlsm")
Set ws = wbtarget.Worksheets("SampleFile") 

ws.Cells(2, 1).CopyFromRecordset rsFinalExp

For i = 2 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    With ws.Cells(i, "B")
         Debug.Print .Parent.Name, .Address(), .Value '<<< for debugging
         Select Case .Value
             Case "FXV": .Value = "FFJ"
             Case "FAM": .Value = "FST"
             Case "FLB": .Value = "FST"
         End Select
     End With 
Next i

wbtarget.Close True 'save on close

Upvotes: 3

Related Questions