Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2016, 01:09 PM   #1
radshar
Newly Registered User
 
Join Date: Aug 2016
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
radshar is on a distinguished road
Upload macro record count

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 by radshar; 08-16-2016 at 01:14 PM. Reason: Additional question
radshar is offline   Reply With Quote
Old 08-16-2016, 02:27 PM   #2
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Upload macro record count

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

Code:
 DoCmd.TransferSpreadsheet acLink, , "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
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 08-16-2016 at 03:06 PM. Reason: Changed name of linked table
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
radshar (08-17-2016)
Old 08-17-2016, 11:02 AM   #3
radshar
Newly Registered User
 
Join Date: Aug 2016
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
radshar is on a distinguished road
Re: Upload macro record count

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??


Quote:
Originally Posted by sneuberg View Post
I believe the easiest way to get the record count without importing it first is to link to it e.g.,

Code:
 DoCmd.TransferSpreadsheet acLink, , "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

radshar is offline   Reply With Quote
Old 08-17-2016, 11:16 AM   #4
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Upload macro record count

Quote:
Originally Posted by radshar View Post
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

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Reply

Tags
access 2013 , count , count macro , macro

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
current record count and total record count on form joe789 Forms 6 01-29-2016 02:46 AM
Using Count and MIN together to retrieve only MIN record with Count tomitzi Queries 9 08-17-2015 07:41 AM
macro to prompt to upload spreadsheet tlindeman Macros 1 07-31-2012 06:34 AM
Group Count in Access 2007 - not record count richxyz Reports 1 08-12-2010 11:57 PM




All times are GMT -8. The time now is 06:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World