Solved

Open a user-selected Excel worksheet, run a macro to format that worksheet, save changes to that worksheet, export to Access

Posted on 2004-03-22
22
1,259 Views
Last Modified: 2007-11-27
Hello I am developing an Access application.

On a weekly basis, user will do the following:

Saves a single Excel file from email
Save file with original name , e.g. EMS Weekly Report 11202003.xls
to a standard, predetermined directory, e.g. c:\data\


User then starts the access application which triggers the following:


dialog box opens, asks for excel file
user chooses excel file

Next, the macros.xls file (which contains the macFormatFile) opens.
Then the chosen Excel file (e.g. ems weekly report 11202003.xls) opens.
Then the macFormatFile macro is fired which prepares the data for import into MS Accessby doing the following:

changes 2 column names to proper Access format
removes the "/" from wage type data
adds a date column populated with formula that pulls a portion of filename
saves this new version of the file


import the Excel spreadsheet using the TransferSpreadsheet action.  The TransferSpreadsheet action will import the first spreadheet by default.  After the temp DB is imported I then run an append query to add the temp file to the various "real" tables.  Turn back on system warnings.


import spreadsheet data into tblTemp
send part to add to tblPayroll
send part to update tblEmployee
send part to update tblSubArea
send part to update tblWageType
purge tblPayrollTemp

CODE:

----ACCESS MACRO macTest3----

      Action            Argument      Value
      RunCode         Function Name:       GetOpenFile ("c:\test\", "Please select the Excel file sent by XXX")
      OpenQuery           Query Name:       qryAppendPayroll
      OpenQuery       Query Name:       qryAppendEmployee
      OpenQuery           Query Name:       qryAppendSubgroup                      
      OpenQuery           Query Name:       qryAppendWageType
      OpenQuery           Query Name:       qryAppendWageType
      OpenQuery           Query Name:       qryPurgePayrollTemp
      SetWarnings          Warnings On:       Yes


----ACCESS FUNCTION GetOpenFile() ----

Function GetOpenFile(Optional varDirectory As Variant, _
    Optional varTitleForDialog As Variant) As Variant

Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
Dim oEx As Object
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
    lngFlags = ahtOFN_FILEMUSTEXIST Or _
                ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
    If IsMissing(varDirectory) Then
        varDirectory = ""
    End If
    If IsMissing(varTitleForDialog) Then
        varTitleForDialog = ""
    End If

    ' Define the filter string and allocate space in the "c"
    ' string Duplicate this line with changes as necessary for
    ' more file templates.
    strFilter = ahtAddFilterItem(strFilter, _
                "Excel (*.xls)")
    ' Now actually call to get the file name.
    varFileName = ahtCommonFileOpenSave( _
                    OpenFile:=True, _
                    InitialDir:=varDirectory, _
                    Filter:=strFilter, _
                    Flags:=lngFlags, _
                    DialogTitle:=varTitleForDialog)
    If Not IsNull(varFileName) Then
        varFileName = TrimNull(varFileName)
    End If
    GetOpenFile = varFileName

   
DoCmd.SetWarnings False
 Set oEx = CreateObject("Excel.Application")
 oEx.Visible = True
 oEx.Workbooks.Open "c:\test\macros.xls"
 oEx.Workbooks.Open GetOpenFile
 oEx.Run "Macros.xls!macFormatFile"
 oEx.Quit
 Set oEx = Nothing

    ' Use the TransferSpreadsheet action to import data between the Microsoft Access
    ' database (.mdb) and the spreadsheet file (.xls).
   
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblPayrollTemp", GetOpenFile, True
     'MsgBox "You selected: " & GetOpenFile
    Debug.Print Hex(lngFlags)
End Function

--EXCEL MACRO macFormatFile--

Sub macFormatFile()

If Range("a1").Value <> "Date" Then

   Worksheets(1).Columns("i").Replace _
    What:="/", Replacement:="", _
    SearchOrder:=xlByColumns, MatchCase:=True

    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Number"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=DATE(((MID((CELL(""filename"")),57,2))+100),(MID((CELL(""filename"")),53,2)),(MID((CELL(""filename"")),55,2)))"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A1968"), Type:=xlFillDefault
    Range("A2:A1968").Select
   
    End If
   
   
End Sub



Here's my problem: This used to work just fine! Now, all of a sudden I am getting prompted by Excel--asking do I want to save changes to macros.xls and the other spreadsheet. I don't know why! No matter what I choose yes/no/cancel -- my tables do not get updated...Now before, I never got prompted and my tables got updated. One wuick mention, I may have accidently messed something up in my code when I tried to fix it to work with Office 2003 (finally realized I had to enable macros)...please help. I can also post my full Module code if that helps(it's not that long). Thanks!
0
Comment
Question by:marnien
  • 11
  • 10
22 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10650034
Here's a couple of ideas to help you with debugging:

Is the macro changing the right spreadsheet?  Make sure the sheet to be imported is active before running the macro.

Change this line:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblPayrollTemp", GetOpenFile, True

Remove the reference to GetOpenFile and replaceit with varFileName.  Dunno if this will help anything, but as it reads it may be trying to call the same function.

Lastly, enable breakpoints in your macro and module.  Follow it through to pinpoint where the failure is.  Is excel not changing the sheet, is the sheet not being saved, it the file being found properly, etc.
0
 

Author Comment

by:marnien
ID: 10650213
Hello, thanks for the quick response...The macro is changing both files--the one specied by the user & the macros.xls (both only have one worksheet)--then when its done changing the files it prompts me to save changes (or discard). Now, I guess I should have somewhere in my code--save changes to the file GetOpenFile so I don't get prompted when the macro tries to close the file--but no changes should have been to macros.xls--I wonder why the macro is all of a sudden running on both worksheets??! I'll try the breakpoints..
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10650293
Greetings marnien!

  Also to prevent application "collision", you should run this entire operation from the application that started the process.  If that is Access, then stay in Access, do not pass control to Excel.  The operation can be comprimised by errors occurring in one application that will reported back to the calling application.

regards
jack
0
 

Author Comment

by:marnien
ID: 10650981
Hello Jack,

so, I should write the macro code right in my Access function or use RunMacro within my macTest3 Macro to call another macro? How does Access know where to run the macro - i.e. what to perform the actions on? As you can probably tell, I am quite new to programming...Thanks. :)

Marnie
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10651162
All actions would be carried out against that "oEx" object that you are instantiating in Access, just further down the object tree.

Macros actions run against, or have an affect on, an object.  A workbook, worksheet, Cell, Range, Chart, whatever...  It doesn't matter where these are being called from, as long as the syntax is correct and the object is in scope, or "in view" of the commands when run.

If you go with putting all the code on the Access side:  the "oEx" object you create in Access is a step toward putting the Excel objects "in view" of the code you will build to replace calling the macro from the workbook.  You just need to change the context of the CODE that is your Excel Macro to operate against the proper object.

I'm going to recommend now that you convert all Macros to VBA code to start learning how these operations are done in code.  
Even being new I would recommend that if you intend to continue your work in Access coding that you also, start thinking in terms of VBA instead of Macros.  Macros are far less powerful, less versatile and much harder to debug and combine in routines.

If you think this is too much for you, I can get another Expert in here to show you how to band-aid what you already have together into a solution.


0
 

Author Comment

by:marnien
ID: 10651430
Okay, I like that idea..moving to all VBA code...so should I have a single module? should I have a single function do everything from opening the dialog box for the user to choose the file to reformatting the selected excel file to saving that file and then importing it into an Access table then run the various append queries? Thanks so much for your help...now, I am new to EE, is it improper for me to ask for your continued help via this single question as I convert my macros to code? Thanks a million.

Marnie
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10651575
When considering module design, think of the following:

  Will breaking the operation into "parts" that can run with just a minor dependency on parameters allow me to REUSE this code later for another similar operation?

  Do some of the operations I am performing here, need to be performed at other times or for other reasons?

I think sometimes these questions are answered during coding, cause you don't think of them until then.

We can indeed continue on to converting the macros.  Have you found the "Convert Macros to VBA" on the menubar yet?
 Tools\Macro\Convert...

Write the steps of your operation out in order of how/when they need doing.  As you add detail to this "pseudo-code" you then begin also adding in bits of actual code to perform that operation or that part of the operation.  you with me so far?
0
 

Author Comment

by:marnien
ID: 10651811
Okay great, thanks to the handy "convert Macros to VBA" tool you pointed out..I've got some new VBA code (please see questions enclosed in ***):

Function macTest3()
On Error GoTo macTest3_Err

    DoCmd.SetWarnings False
    Call GetOpenFile("c:\test\", "Please select the Excel file sent by BPHC")
   ' ***Here should I put a call to a new function that takes care of the stuff I had my Excel macro doing? ***
   ' ***Now, I could put this next section into its own function, right? In that case my function macTest3() could be what they call
   '       a wrapper function?***
    DoCmd.OpenQuery "qryAppendPayroll", acNormal, acEdit
    DoCmd.OpenQuery "qryAppendEmployee", acNormal, acEdit
    DoCmd.OpenQuery "qryAppendSubgroup", acNormal, acEdit
    DoCmd.OpenQuery "qryAppendWageType", acNormal, acEdit
    DoCmd.OpenQuery "qryAppendWageType", acNormal, acEdit
    DoCmd.OpenQuery "qryPurgePayrollTemp", acNormal, acEdit
    DoCmd.SetWarnings True


macTest3_Exit:
    Exit Function

macTest3_Err:
    MsgBox Error$
    Resume macTest3_Exit

End Function

0
 
LVL 32

Expert Comment

by:jadedata
ID: 10652179
Instead of opening the Excel file to launch the macro inside it, you will now ONLY need the location and name of the file.  

Instead of opening it you're going to set it up as an Excel Object and use it from there.

  Public Function MarnieDidIt()

   dim sFilePath as String, sFileName as String
   dim xlObj as Object, xlWB as workbook, xlWS as Worksheet

   '*** Get file location name with common dialog
   '*** http://www.mvps.org/access/api/api0001.htm for a "drop in" module
   '*** the above allow you to give the user a Win std browse control and retrieve the selected file path/name in a string variable.
   '***split file path and name into component

   Set xlObj = New Excel.application
   xlObj.Visible = True
   xlObj.Workbooks.Open sFilePath & sFileName
   xlObj.Workbooks.Open sFileName
   set xlWB = xlObj.Workbooks("sFileName")
   set xlWS = xlWB.worksheets("WorksheetName")  '<----is this a standard name for these??

   ' ***Here should I put a call to a new function that takes care of the stuff I had my Excel macro doing? ***
   '((( I do not use SELECT in VBA code while the actions are visible on screen
   '((( I simply take whatever action is needed against the Range that it needs to apply to...
   '((( the below code needs to be put into "context by using the xlWS object set earlier to make edits on the worksheet.

   If xlws.Range("a1").Value <> "Date" Then
     xlws.Columns("i").Replace  What:="/", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
     xlWS.Range("K1").Select
     ActiveCell.FormulaR1C1 = "Number"
     Range("L1").Select
     ActiveCell.FormulaR1C1 = "Amount"
     ActiveWindow.ScrollColumn = 1
     Columns("A:A").Select
     Selection.Insert Shift:=xlToRight
     Range("A1").Select
     ActiveCell.FormulaR1C1 = "Date"
     Range("A2").Select
     ActiveCell.FormulaR1C1 = "=DATE(((MID((CELL(""filename"")),57,2))+100),(MID((CELL(""filename"")),53,2)),(MID((CELL(""filename"")),55,2)))"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A1968"), Type:=xlFillDefault
    Range("A2:A1968").Select
   
    End If
   
   
   ' ***Now, I could put this next section into its own function, right? In that case my function macTest3() could be what they call
   '       a wrapper function?***

    'DoCmd.OpenQuery "qryAppendPayroll", acNormal, acEdit  'REPLACE these openquerys with lines like
    Currentdb.execute "qryAppendPayroll", dbfailonerror       '<------

    DoCmd.OpenQuery "qryAppendEmployee", acNormal, acEdit
    DoCmd.OpenQuery "qryAppendSubgroup", acNormal, acEdit
    DoCmd.OpenQuery "qryAppendWageType", acNormal, acEdit
    DoCmd.OpenQuery "qryAppendWageType", acNormal, acEdit
    DoCmd.OpenQuery "qryPurgePayrollTemp", acNormal, acEdit


The above is a bit "hackish" but I hope you are getting the idea of how to fashion this into a single streamlined function...
0
 

Author Comment

by:marnien
ID: 10653700
Thanks. Now, I actually had already gone to that site to get the code I'm using for user to choose the excel file. Problem is, it is a bit confusing how to use it...do I need the entire module posted there? I put the module in my db application but I just don't know what it all means and if I need it all. It works as I have it but I do get a vb runtime error if I hit cancel on the dialog box...it looks like the entire module does some error handling but hmm...I get a bit lost. Also, what do you mean by "split file path and name into component"? Do I need to do that? One more thing, are you suggesting I just have a single function in my app (at least for the stuff I've asked you about?) Would it be neater if I had one "wrapper function" named, using your cute name, MarnieDidIt() that called 3 functions - GetFile(), FormatFile(), ImportFile() ? Or does it matter? Thanks for your continued help.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10653848
You can have one Module with separate functions, if those functions may have value as a standalone procedure.

For now One big long function will do it.  It will be good practice and it can be broken into smaller chunks later if/when needed.

The code for the API Common Dialog function goes in a module by itself.  

The cancel button probably returns a blank string that another following line of your code can't handle.

I'm in EST and it's dinner time now but I will be back in the morning.

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:marnien
ID: 10653859
Jack, oh I forgot, I had a question about the following:

   Dim sFilePath As String, sFileName As String
   Dim xlObj As Object, xlWB As Workbook, xlWS As Worksheet

   Set xlObj = CreateObject("Excel.Application")
   xlObj.Visible = True
   xlObj.Workbooks.Open sFilePath & sFileName
   xlObj.Workbooks.Open sFileName
   Set xlWB = xlObj.Workbooks("sFileName")
   Set xlWS = xlWB.worksheets("WorksheetName")  '<----is this a standard name for these??

I get an error trying to compile our code...Access gives a compile error "User-defined type not defined" and stops at xlWB as Workbook...does that mean anything? Also, what did you mean by "<----is this a standard name for these??" Thanks again. :) Marnie
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10654776
You need to include the MS Excel 10 Obj Library in the References

Put any module in design view (VB Window)
Select Menu Tools\References

Find and select the MS Excel version for your Office Suite.
Select it (check the box)
Close the reference window

re-open the references to assure that it stuck.
0
 

Author Comment

by:marnien
ID: 10657518
Thanks for the above...I'm actually creating this application for a friend so I can't do too much work on it while I'm at my real job...(like now!)...particularly today since I'm in meetings for a good part of the day. Now unfortunately I have different versions of Office at home (2003) and work (2000) do you think I will get into trouble working in both? I've already run into lots of problems in Office 2003 - it seems you have to add some specific lines of code in VBA in order to run the code due to ?improved? security in Office 2003...I guess I'll see..Thanks for all your help so far. Perhaps around lunchtime I'll be able to look at it some more.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10658042
Develop the application in the 2000.  I think the security thing is a conspiracy against programmers.
0
 

Author Comment

by:marnien
ID: 10673221
Well, I'm back (Jack)...having problems as I expected...first problem is, it seems the GetFileName function is running twice for some reason --or at least some portion of it is running twice because once I choose the excel file I want to open in the dialog box, Excel is displayed on my screen with no active worksheet..Access is minimized, when I click on Access in my taskbar I get another dialog box prompting me to choose my Excel file again...I don't know why...here are the only 2 modules I have in my application. I am now using NO macros :)  Note, I am using the code exactly from the website you directed me to...I put back the testing piece where a message box displays the name of the chosen Excel file...(with this, I am seeing the same problem --now I am getting the message box telling me which file I chose--when I click ok on that message box and go back to Access, I again get a prompt to select the Excel file...Now, this only happens twice, then the actual Excel worksheet I chose is shown and the remainder of the code has been run on it--i.e. the worksheet gets formatted) Any thoughts on what is happening? Thanks for your continued help!! -Marnie

--------------MODULE 1--------------------

Option Compare Database

'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    strFilter As String
    strCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    strFile As String
    nMaxFile As Long
    strFileTitle As String
    nMaxFileTitle As Long
    strInitialDir As String
    strTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    strDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
    Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000


Function GetOpenFile(Optional varDirectory As Variant, _
    Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
    lngFlags = ahtOFN_FILEMUSTEXIST Or _
                ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
    If IsMissing(varDirectory) Then
        varDirectory = ""
    End If
    If IsMissing(varTitleForDialog) Then
        varTitleForDialog = ""
    End If

    ' Define the filter string and allocate space in the "c"
    ' string Duplicate this line with changes as necessary for
    ' more file templates.
    'strFilter = ahtAddFilterItem(strFilter, _
                '"Excel (*.xls)")
    ' Now actually call to get the file name.
    varFileName = ahtCommonFileOpenSave( _
                    OpenFile:=True, _
                    InitialDir:=varDirectory, _
                    Filter:=strFilter, _
                    Flags:=lngFlags, _
                    DialogTitle:=varTitleForDialog)
    If Not IsNull(varFileName) Then
        varFileName = TrimNull(varFileName)
    End If
    GetOpenFile = varFileName
End Function

Function ahtCommonFileOpenSave( _
            Optional ByRef Flags As Variant, _
            Optional ByVal InitialDir As Variant, _
            Optional ByVal Filter As Variant, _
            Optional ByVal FilterIndex As Variant, _
            Optional ByVal DefaultExt As Variant, _
            Optional ByVal FileName As Variant, _
            Optional ByVal DialogTitle As Variant, _
            Optional ByVal hwnd As Variant, _
            Optional ByVal OpenFile As Variant) As Variant
' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
    ' Give the dialog a caption title.
    If IsMissing(InitialDir) Then InitialDir = CurDir
    If IsMissing(Filter) Then Filter = ""
    If IsMissing(FilterIndex) Then FilterIndex = 1
    If IsMissing(Flags) Then Flags = 0&
    If IsMissing(DefaultExt) Then DefaultExt = ""
    If IsMissing(FileName) Then FileName = ""
    If IsMissing(DialogTitle) Then DialogTitle = ""
    If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
    If IsMissing(OpenFile) Then OpenFile = True
    ' Allocate string space for the returned strings.
    strFileName = Left(FileName & String(256, 0), 256)
    strFileTitle = String(256, 0)
    ' Set up the data structure before you call the function
    With OFN
        .lStructSize = Len(OFN)
        .hwndOwner = hwnd
        .strFilter = Filter
        .nFilterIndex = FilterIndex
        .strFile = strFileName
        .nMaxFile = Len(strFileName)
        .strFileTitle = strFileTitle
        .nMaxFileTitle = Len(strFileTitle)
        .strTitle = DialogTitle
        .Flags = Flags
        .strDefExt = DefaultExt
        .strInitialDir = InitialDir
        ' Didn't think most people would want to deal with
        ' these options.
        .hInstance = 0
        '.strCustomFilter = ""
        '.nMaxCustFilter = 0
        .lpfnHook = 0
        'New for NT 4.0
        .strCustomFilter = String(255, 0)
        .nMaxCustFilter = 255
    End With
    ' This will pass the desired data structure to the
    ' Windows API, which will in turn it uses to display
    ' the Open/Save As Dialog.
    If OpenFile Then
        fResult = aht_apiGetOpenFileName(OFN)
    Else
        fResult = aht_apiGetSaveFileName(OFN)
    End If

    ' The function call filled in the strFileTitle member
    ' of the structure. You'll have to write special code
    ' to retrieve that if you're interested.
    If fResult Then
        ' You might care to check the Flags member of the
        ' structure to get information about the chosen file.
        ' In this example, if you bothered to pass in a
        ' value for Flags, we'll fill it in with the outgoing
        ' Flags value.
        If Not IsMissing(Flags) Then Flags = OFN.Flags
        ahtCommonFileOpenSave = TrimNull(OFN.strFile)
    Else
        ahtCommonFileOpenSave = vbNullString
    End If
End Function

Function ahtAddFilterItem(strFilter As String, _
    strDescription As String, Optional varItem As Variant) As String
' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases"), a null character, the skeleton
' (like "*.mdb;*.mda") and a final null character.

    If IsMissing(varItem) Then varItem = "*.*"
    ahtAddFilterItem = strFilter & _
                strDescription & vbNullChar & _
                varItem & vbNullChar
End Function

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
    intPos = InStr(strItem, vbNullChar)
    If intPos > 0 Then
        TrimNull = Left(strItem, intPos - 1)
    Else
        TrimNull = strItem
    End If
End Function


------------MODULE 2---------------

Public Function MarnieDidIt()

   Dim sFilePath As String, sFileName As String
   Dim xlObj As Object, xlWB As workbook, xlWS As Worksheet

    Dim strFilter As String
    Dim lngFlags As Long
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
    MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", _
        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
        DialogTitle:="Please select the Excel file sent by BPHC")
    ' Since you passed in a variable for lngFlags,
    ' the function places the output flags value in the variable.
    Debug.Print Hex(lngFlags)
   
   Set xlObj = New Excel.Application
   xlObj.Visible = True
   xlObj.Workbooks.Open GetOpenFile
   'xlObj.Workbooks.Open sFileName
   Set xlWB = xlObj.Workbooks(1)
   Set xlWS = xlWB.worksheets(1)


   If xlWS.Range("a1").Value <> "Date" Then
     xlWS.Columns("i").Replace What:="/", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
     xlWS.Range("K1").Value = "Number"
     xlWS.Range("L1").Value = "Amount"
     xlWS.Range("A1").Value = "Date"
     xlWS.Range("A2:A1968").Value = "=DATE(((MID((CELL(""filename"")),57,2))+100),(MID((CELL(""filename"")),53,2)),(MID((CELL(""filename"")),55,2)))"
   
    End If
 
    CurrentDb.Execute "qryAppendPayroll", dbfailonerror
    CurrentDb.Execute "qryAppendEmployee", dbfailonerror
    CurrentDb.Execute "qryAppendSubgroup", dbfailonerror
    CurrentDb.Execute "qryAppendWageType", dbfailonerror
    CurrentDb.Execute "qryPurgePayrollTemp", dbfailonerror

End Function
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10677718
very good!!!  I thought you said you never did this before?

The FileOpen command runs twice because you are calling it twice, or something like it twice.  The first and only time it is called you should be storing the name of the files and its path to a variable to be used when you use it to set an Excel object workbook.

Module 1 should contain only the code for the common browse control activation and use, and it appears that it does.  just confirm this.
(I call this bas_API_CommDlg)

Module 2 Lets move thru this a few lines at a time to keep confusion to a minimum...


'***File acquisition:
MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", _
        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
        DialogTitle:="Please select the Excel file sent by BPHC")
    ' Since you passed in a variable for lngFlags,
    ' the function places the output flags value in the variable.
    Debug.Print Hex(lngFlags)

This is where you should be using the ahtCommonFileOpenSave to acquire the path/name of the file and storing it to a var

FilePathName = ahtCommonFileOpenSave(.....
if  MsgBox ("You selected:" & FilePathName,vbyesno+vbquestion,"confirm your selection") = vbno then
  'exit routine
endif

'this routine breaks down the full filepathname into two separate strings...
(you'll need these dimmed in the beginning...)
dim sFilePathName as string, sFilePath as string, sFileName as string, slash as Integer

  For slash = Len(sFilePathName ) To 1 Step -1
    If Mid$(sFilePathName , slash, 1) = "\" Then
      sFilePath = Left$(sFilePathName , slash)
      sFileName = Right$(sFilePathName , Len(sFilePathName ) - slash)
      GoTo MarnieDidIt_GotNewFolder:
    End If
  Next slash

note:  You need the full sFilePathName to .Open the excel workbook
but only the sFilename to set the object which (should) be set by name rather than number
Set xlWB = xlObj.Workbooks(sFileName)

this prevent you from setting the wrong workbook if the user should already have excel open for any reason..

more when you've read and this... Questions?  Ask em.


0
 

Author Comment

by:marnien
ID: 10692584
Oh my..you've lost me...I am so confused...now, I meant it when I said I haven't done this before...all that nice looking code - I got from the web page you directed me to and from you! I haven't a clue on how this all goes together! eek! I think the message boxes have gotten me confused...I only need them for testing purposes, right..to make sure I'm opening the file I think I'm opening? Aaargh. I'm sorry...I wish I understood this better..I don't know what sections to put where...I understand how you breakdown the filepathname into the separate file name and filepath but I am not sure what the difference is between opening the excel workbook and setting the object...I guess it is the word "set" I don't understand here...Thanks for your continued patience as I try to "get" this all... :|  -Marnie
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10692899
Stop!!
Slow down,...
Step away from the keyboard....

one thing at a time.

Lets concentrate on
"Indentifying the Excel workbook to use for the operation"

How are we doing there?

We want the file browse control to open
the users selects a file
and the variable gets set with that file path/name
then the system will break the two into the vars for sFilePath and sFileName.

then we move on...
0
 

Author Comment

by:marnien
ID: 10696733
Hi Jack,

Okay, so I have a command button named something like "Import Data" which has in its on-click event a call to the MarnieDidIt() function..or does it call another function from my other module (the module you call "bas_API_CommDlg")? Probably a stupid question...

marnie
0
 
LVL 32

Accepted Solution

by:
jadedata earned 500 total points
ID: 10698637
The button code can call one function, the can call other functions.  Think of it like tree branches

The Button (OnClick) calls
  MarnieDidIt (we might want a more description name on this in a little while...)

MarnieDidIt function calls
  the routine to find and "define" the xls file to be used from the API module
  runs the code to take the xls file and fill it from the recordset.

you with that?

For now we just want to know that the API part is properly Identifying the right workbook file and storing that into a variable, and then splitting it into a Path and a Name for use later...
0
 

Author Comment

by:marnien
ID: 10737341
Hi Jack, thanks for your help with this question. I'm going to close this and open another as I come across additional problems...I am kind of concentrating on my reports for this database application right now...I will need to revisit the code for bringing in the excel data. Thanks again. You have answered my questions so far!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now