Upload macro record count (1 Viewer)

radshar

Registered User
Joined
Aug 5, 2016
Messages
32
Hello,

I have the below macro i'm using to upload an excel file into a table in access. I want there to be a prompt before the upload happens to confirm the number of records that are being added...

Also for the same code I want a way for there to be a prompt if the user tries to upload a file with the same name a previously uploaded file. is this possible?

Code:
Private Sub Command0_Click() 'INPUT PENDING REPORT
 Dim objXLApp As Object
Dim File_import As Variant
                          
        Set objXLApp = CreateObject("excel.application")
        
          ' Ask the user for the file name to open.
        File_import = objXLApp.GetOpenFilename
        
    If File_import = False Then
        MsgBox "No file selected. Import cancelled"
        Exit Sub
        Else
            DoCmd.TransferSpreadsheet acImport, , "Pending Report - Cumulative", File_import, True
            
            MsgBox "Import completed"
    
    End If
    
    
    End Sub
 
Last edited:

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
I believe the easiest way to get the record count without importing it first is to link to it e.g.,

Code:
 DoCmd.TransferSpreadsheet [COLOR="Blue"]acLink[/COLOR], , "Pending Report - Cumulative Link", File_import, True
Then get a count
Code:
DCount("*", "[Pending Report - Cumulative Link]")
The delete the link

Code:
CurrentDb.Execute "Drop Table [Pending Report - Cumulative Link]"
Prompt the user with a msgbox and imported it for real or not.

As far as checking previous file names you will need a table to store them. Let say tblFileNames with ID, and FileName as fields.

Then each time you import you check this table something like
Code:
If DCount("*", "[tblFileNames]", "[FileName] = '" & GetFileName(File_import) & "'") > 0 Then
    'do whatever

Then insert the imported file name into this table something like:
Code:
CurrentDb.Execute "INSERT INTO tblFileNames (FileName) VALUES ( '" & GetFileName(File_import) & "');"


The code for GetFileName used in the DCount and INSERT is:


Code:
Public Function GetFileName(ByVal FullPath As String) As String
'**************************************************
'*  Created By:     Scott L Prince
'*  Created On:     10/2/13
'*  Modified:
'*  Purpose:        Returns a file name from the full path provided.
'*  Parameters:     Full path including file name
'*  Output:         File name, or empty string if no file name could be determined.
'*  Comments:
'**************************************************
On Error GoTo GetFileName_Err
 
    'Defaults
    GetFileName = ""
    'Only necessary if a FullPath has actually been passed.
    If FullPath <> "" Then
        Dim BackslashLocation As Long   'Location of the last "/" or "\" in the path
        'Locate the FINAL backslash.
        BackslashLocation = InStrRev(FullPath, "\")
 
        'If no "\" was found, then check for "/" (sharepoint file structure).
        If BackslashLocation = 0 Then BackslashLocation = InStrRev(FullPath, "/")
 
        'Determine if a slash was found.
        If BackslashLocation > 0 Then
 
            'A slash was found, so return the file name.
            GetFileName = Right(FullPath, Len(FullPath) - BackslashLocation)
        Else
 
            'No slash found, so return FullPath as the file name.
            GetFileName = FullPath
        End If
    End If
 
GetFileName_Exit:
    Exit Function
 
GetFileName_Err:
    MsgBox "An error has occurred in procedure 'GetFileName'!" & vbCrLf & vbCrLf & _
           "Error:" & vbTab & vbTab & Err.Number & vbCrLf & _
           "Description:" & vbTab & Err.Description, vbOKOnly + vbCritical
    Resume GetFileName_Exit
End Function
 
Last edited:

radshar

Registered User
Joined
Aug 5, 2016
Messages
32
Hi sneuberg,

I'll give that a shot...one thing the second code for the count does not work...its highlighted in red. Any ideas??


I believe the easiest way to get the record count without importing it first is to link to it e.g.,

Code:
 DoCmd.TransferSpreadsheet [COLOR=blue]acLink[/COLOR], , "Pending Report - Cumulative Link", File_import, True
Then get a count
Code:
DCount("*", "[Pending Report - Cumulative Link]")
The delete the link

Code:
CurrentDb.Execute "Drop Table [Pending Report - Cumulative Link]"
Prompt the user with a msgbox and imported it for real or not.

As far as checking previous file names you will need a table to store them. Let say tblFileNames with ID, and FileName as fields.

Then each time you import you check this table something like
Code:
If DCount("*", "[tblFileNames]", "[FileName] = '" & GetFileName(File_import) & "'") > 0 Then
    'do whatever
Then insert the imported file name into this table something like:
Code:
CurrentDb.Execute "INSERT INTO tblFileNames (FileName) VALUES ( '" & GetFileName(File_import) & "');"
The code for GetFileName used in the DCount and INSERT is:


Code:
Public Function GetFileName(ByVal FullPath As String) As String
'**************************************************
'*  Created By:     Scott L Prince
'*  Created On:     10/2/13
'*  Modified:
'*  Purpose:        Returns a file name from the full path provided.
'*  Parameters:     Full path including file name
'*  Output:         File name, or empty string if no file name could be determined.
'*  Comments:
'**************************************************
On Error GoTo GetFileName_Err
 
    'Defaults
    GetFileName = ""
    'Only necessary if a FullPath has actually been passed.
    If FullPath <> "" Then
        Dim BackslashLocation As Long   'Location of the last "/" or "\" in the path
        'Locate the FINAL backslash.
        BackslashLocation = InStrRev(FullPath, "\")
 
        'If no "\" was found, then check for "/" (sharepoint file structure).
        If BackslashLocation = 0 Then BackslashLocation = InStrRev(FullPath, "/")
 
        'Determine if a slash was found.
        If BackslashLocation > 0 Then
 
            'A slash was found, so return the file name.
            GetFileName = Right(FullPath, Len(FullPath) - BackslashLocation)
        Else
 
            'No slash found, so return FullPath as the file name.
            GetFileName = FullPath
        End If
    End If
 
GetFileName_Exit:
    Exit Function
 
GetFileName_Err:
    MsgBox "An error has occurred in procedure 'GetFileName'!" & vbCrLf & vbCrLf & _
           "Error:" & vbTab & vbTab & Err.Number & vbCrLf & _
           "Description:" & vbTab & Err.Description, vbOKOnly + vbCritical
    Resume GetFileName_Exit
End Function
 

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
Hi sneuberg,

I'll give that a shot...one thing the second code for the count does not work...its highlighted in red. Any ideas??
That was just an example of DCount. You have to do something with it like perhaps

Code:
Dim Count As Long
Count = DCount("*", "[Pending Report - Cumulative Link]")
If MsgBox("The number of records is " & Count & " Do you want to import? ", vbYesNo) = vbNo Then
    Exit Sub
End If
 'do the import
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom