Access VBA - Picking up a predefined file

[Cjdash]

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

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

 

[ranman256]

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.

 

[Cjdash]

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

 

[xenou]

it's currently opening the dialog box where I'd usually select the folder, but not selecting the relevant file automatically

2. What's in Cell D7 of the workbook, a file path ("C:\SomeFolder") or a full file name ("C:\SomeFolder\SomeFile.xlsx")?
3. 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).

 

[Cjdash]

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

 

[Cjdash]

I've now changed the code to remove the dialog box and this has worked!

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

 

[xenou]

Looks good.
Make sure when you finish you close the workbook and quit Excel:

xlWb.Close False
xlApp.Quit