Where is code stored?

After exporting a tblMain from Access 2007 to mySpreadSheet.xlsx I need to manipulate the columns and etc.  Of course my Initial Sub Routine is in Access VBE;  Do I continue with all the other mentioned code doing the manipulation in the same Access code procedure, or do I "transfer" to Excel and Pick up where my Access Export ends?

I'm that new to integrating Access/Excel...

Thanks,

Jim
JMay44Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can do it Access, but if your manipulation involves complex processing, continue the codes in the excel file.
0
JMay44Author Commented:
So it all can be done in Access from within the same Sub?  I don't think it's going to be that "Complicated"...

Jim
0
Rey Obrero (Capricorn1)Commented:
what are the things you wanted done?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Patrick MatthewsCommented:
As cap1 mentioned, it is not particularly difficult to manipulate Excel from Access, so if you can describe what needs to happen (and even better, provide a mock-up in Excel), he, I, or any of a number of Experts can help you.

Patrick
0
JMay44Author Commented:
I will work on tomorrow and report back -- Thanks Patrick
0
Helen FeddemaCommented:
You can record macros in Excel, and then copy and paste the code into Access modules.  Of course, you will need to tidy it up, and make an Excel application variable to use as the top-level object, but this can be helpful.
Here is some sample code to work with an Excel workbook:
Public Sub SearchByLastName(strLastName As String)
'Created by Helen Feddema 5-Mar-2008
'Last modified 5-Mar-2008

On Error GoTo ErrorHandler
   
   'Open workbook
   strDocsPath = "c:\agentdata\"
   strWorkbookName = "MLSAGENT.xls"
   strWorkbookNameAndPath = strDocsPath & strWorkbookName
   Set appExcel = GetObject(, "Excel.Application")
   
   'Check whether workbook is already open
On Error Resume Next
   
   Set wkb = appExcel.Workbooks(strWorkbookNameAndPath)
   If wkb Is Nothing Then
      'Open workbook
      Set wkb = appExcel.Workbooks.Open(strWorkbookNameAndPath)
   End If
   
On Error GoTo ErrorHandler
   
   Set wks = wkb.Sheets(1)
   wks.Activate
   appExcel.Visible = True
   Set rng = wks.range("A1")
   rng.Select
   
   'Search for last name
   wks.Columns("BB:BB").Select
   
On Error Resume Next
   Set rng = appExcel.Selection.Find(What:=strLastName, _
      After:=ActiveCell, _
      LookIn:=xlFormulas, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, _
      MatchCase:=False, _
      SearchFormat:=False)
   rng.Select
   
   If rng Is Nothing Then
      strTitle = "Not found"
      strPrompt = "Last name " & strLastName _
         & " not found in worksheet; canceling"
      MsgBox prompt:=strPrompt, _
         Buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   End If
   
   'Set rng = appExcel.Selection
   rng.Select
   lngRow = rng.Row
   strRange = "AU" & CStr(lngRow)
   Set rng = wks.range(strRange)
   strFullName = rng.Text
   
   strTitle = "Found"
   strPrompt = "The contact's full name is " & strFullName _
      & "; is this correct?"
   intReturn = MsgBox(prompt:=strPrompt, _
      Buttons:=vbQuestion + vbYesNo, _
      Title:=strTitle)

   If intReturn = vbYes Then
      'Check whether there already is a contact for this person
      Set nms = Application.GetNamespace("MAPI")
      Set fldContacts = nms.GetDefaultFolder(olFolderContacts)
      strSearch = "[FullName] = " & Chr(39) & strFullName & Chr(39)
      Debug.Print "Search string: " & strSearch
      Set con = fldContacts(strSearch)
      If con Is Nothing Then
         Call CreateContact(lngRow, wks)
      Else
         Call UpdateContact(con, lngRow, wks)
         GoTo ErrorHandlerExit
      End If
   
   ElseIf intReturn = vbNo Then
      GoTo NextMatch
   End If

NextMatch:

   Set rng = appExcel.Selection.FindNext(What:=strLastName, _
      After:=ActiveCell, _
      LookIn:=xlFormulas, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, _
      MatchCase:=False, _
      SearchFormat:=False)
   
   If rng Is Nothing Then
      strTitle = "Not found"
      strPrompt = "Another " & strLastName _
         & " not found in worksheet; canceling"
      MsgBox prompt:=strPrompt, _
         Buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   Else
      lngRow = rng.Row
      strRange = "AU" & CStr(lngRow)
      Set rng = wks.range(strRange)
      strFullName = rng.Text
      
      strTitle = "Found"
      strPrompt = "The contact's full name is " & strFullName _
         & "; is this correct?"
      intReturn = MsgBox(prompt:=strPrompt, _
         Buttons:=vbQuestion + vbYesNo, _
         Title:=strTitle)
   
      If intReturn = vbYes Then
         'Check whether there already is a contact for this person
         Set nms = Application.GetNamespace("MAPI")
         Set fldContacts = nms.GetDefaultFolder(olFolderContacts)
         strSearch = "[FullName] = " & Chr(39) & strFullName & Chr(39)
         Debug.Print "Search string: " & strSearch
         Set con = fldContacts(strSearch)
         If con Is Nothing Then
            Call CreateContact(lngRow, wks)
         Else
            Call UpdateContact(con, lngRow, wks)
         End If
      ElseIf intReturn = vbNo Then
         GoTo NextMatch
      End If
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   'Excel is not running; open Excel with CreateObject
   If Err.Number = 429 Then
      Set appExcel = CreateObject("Excel.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: "
      Resume ErrorHandlerExit
   End If

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.