Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-26-2019, 10:36 AM   #16
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,109
Thanks: 25
Thanked 551 Times in 539 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Help with Prompt user to input file name and location

Hi. Here's your code with some recommended changes. Please try it out and let us know what happens.
Code:
Private Sub Import_File_Click()  

On Error GoTo Err_Import_File_Click  

Dim strFile_Path As String 
Dim strTable As String  

'Prompt user for file path 
strFile_Path = InputBox("Please enter file path and name (e.g. C:\Folder\File.xlsx)") 

'Prompt user for name of table to create for imported data 
strTable = InputBox("Please enter name of new table")  

'Import file, using inputted file path and table name 
If Dir(strFile_Path)<>"" Then
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12xml, strTable, strFile_Path
Else
     MsgBox "The file path you entered is not valid.", vbInformation, "Error"
End If

Exit_Import_File_Click: 
    Exit Sub  

Err_Import_File_Click: 
     If Err.Number = 3011 Then 
        MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path" 
     Else 
        MsgBox Err.Description 
    End If 
    Resume Exit_Import_File_Click  

End Sub

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-26-2019, 11:23 AM   #17
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,484
Thanks: 378
Thanked 595 Times in 577 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Help with Prompt user to input file name and location

You can set a default path for the FileDialog?

I'm thinking, if the user cannot select a correct file from the dialog, typing in the correct name and path is going to be a chore?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 01-27-2019, 09:52 AM   #18
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 378
Thanks: 57
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Help with Prompt user to input file name and location

thanks for the code, apologies for the delay. As mentioned earlier i wanted user to type the file name rather selecting from the FileDialog.

I have tried to modify my code and it allows me to import excel into access. The only problem is it disregards the file location in strPath and copies the file placed in Documents. (C:\Users\lookforsmt\Documents)

Code:
Private Sub Import_File_Click()

    On Error GoTo Err_Import_File_Click
    
    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String
    Dim binHasFieldNames As Boolean
 
'Change this next line to True if the first row in Excel worksheet
'has field names

    binHasFieldNames = True

'    strPath = InputBox("Please enter file path")
    strPath = "E:\Access4.0\Reports\New folder\"
    strTable = InputBox("Please enter name of new table")

'    strFile = Dir(strPath & "test.xlsx")

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl_test", strTable, True
    

Exit_Import_File_Click:
Exit Sub

Err_Import_File_Click:

If Err.Number = 3011 Then
MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path"
Else
MsgBox Err.Description
End If
Resume Exit_Import_File_Click
End Sub
How can i change the location from Documents to strPath location.

lookforsmt is offline   Reply With Quote
Old 01-27-2019, 10:15 AM   #19
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,075
Thanks: 3
Thanked 451 Times in 444 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Help with Prompt user to input file name and location

@lookforsmt, in #8 you wrote
Quote:
i dont want to use file dialog method as the user needs to scroll through many files which is highly likely to select the wrong file.
I think you are successfully proving that it is harder to type in the right file and path.
In your statement
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl_test", strTable, True
you are trying to import the contents of strTable. which you have prompted the user to enter the name of a new table.
Insert a few debug.prints in your code to see just what you are doing, eg
Code:
debug.print strTable, strFile
Cronk is offline   Reply With Quote
Old 01-27-2019, 11:08 AM   #20
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 378
Thanks: 57
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Help with Prompt user to input file name and location

Dear Cronk, where do i place the code in the module to debug.

in the IW should i type ?strTable or ? strFile
lookforsmt is offline   Reply With Quote
Old 01-27-2019, 03:17 PM   #21
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,075
Thanks: 3
Thanked 451 Times in 444 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Help with Prompt user to input file name and location

Add the debug.print line just before the docmd.transferspreadsheet line.


You can use ? varName in the IW but you'd need to add a break point on the docmd.transferspreadsheet line.

Cronk is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
TransferSpreadsheet with prompt for file location FoolzRailer Modules & VBA 8 04-27-2016 03:50 AM
prompt user for file name when exporting delimited txt file dmg Modules & VBA 0 09-21-2012 03:31 AM
Best way to prompt user input? ChuiceBox Modules & VBA 1 12-06-2011 06:59 AM
TransferSpreadsheet Prompt User for File karl009 Modules & VBA 3 04-16-2010 09:23 AM
User prompt to enter file location Adrianna Macros 0 05-06-2002 06:47 PM




All times are GMT -8. The time now is 05:39 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