• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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
0
JMay44
Asked:
JMay44
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now