Access VBA - Picking up a predefined file

Cjdash

New Member
Joined
Jun 1, 2018
Messages
7
Hi,

Having issues writing the below and getting it to work. I want it to be able to use a cell in an excel document to determine which file needs to be picked up, it's currently opening the dialog box where I'd usually select the folder, but not selecting the relevant file automatically. The filepath is stored within cell D7 of C:\Morning Imports.xlsm

Any help is greatly appreciated

Code:
Function ImportCancellationAuto()


    Dim fd As Object, fileName As String
    Dim varFile As Variant


    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSheet As Excel.Worksheet


    Set xlApp = New Excel.Application
With xlApp
    Set xlWB = .Workbooks.Open("[COLOR=#2A2A2A][FONT='inherit']C:\[/FONT][/COLOR]Morning Imports.xlsm", , False)
    Set xlSheet = xlWB.Worksheets("Sheet1") 
End With


    varFile = xlSheet.Range("D7")
    
    Set fd = Application.FileDialog(3)
 
    fd.AllowMultiSelect = False
 
    If fd.Show = True Then
For Each varFile In fd.SelectedItems
    DoCmd.TransferText acImportDelim, "OrderCancellation_Specification_2", "order_Cancellation", "varFile"
    fileName = varFile
    Next
Else
   MsgBox "You have cancelled the import."
   
End If
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
you have fd.AllowMultiSelect = False

then you cycle thru the list ,but it is ONLY 1.
For Each varFile In fd.SelectedItems

fd.AllowMultiSelect = TRUE ALLOWS MANY FILES.
 
Last edited:
Upvote 0
Hi Ranman,

Setting this option to True doesn't change the outcome when I run it. It still opens up file explorer and doesn't pick up the file path that's been populated in excel

I only need one file to be selected, I won't ever have a scenario where I need two files to be picked up
 
Upvote 0
Code:
it's currently opening the dialog box where I'd usually select the folder, but not selecting the relevant file automatically
  1. What's in Cell D7 of the workbook, a file path ("C:\SomeFolder") or a full file name ("C:\SomeFolder\SomeFile.xlsx")?
  2. Are you trying to do this automatically? I'm confused why you are using a DIALOG BOX when you want an automated process (which means running without a human having to be there to click dialog box buttons).
 
Last edited:
Upvote 0
Hi Xenou,

1. Cell D7 has the full file path, including the name of file that will need to picked up

2. It's been a manual process historically, I'm now trying to make it automatic. I'm new to vba and have been trying to edit the preexisting code to get it to work
 
Upvote 0
I've now changed the code to remove the dialog box and this has worked!

Code:
Function ImportCancellationAuto()


    Dim fd As Object, fileName As String
    Dim varFile As String


    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSheet As Excel.Worksheet


    Set xlApp = New Excel.Application
With xlApp
    Set xlWB = .Workbooks.Open("\\cor-str-fs01.corp.cjclark.com\shared$\clear\Online\Automation Home Files\Morning Imports.xlsm", , False)
    Set xlSheet = xlWB.Worksheets("PickUp") 'Name of tab you want to select from
End With


    varFile = xlSheet.Range("D7")
    
    DoCmd.TransferText acImportDelim, "OrderCancellation_Specification_2", "order_Cancellation", varFile
 
Upvote 0
Looks good.
Make sure when you finish you close the workbook and quit Excel:

Code:
xlWb.Close False
xlApp.Quit
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top