Get Path store in Access Table

[bama4954]

I want the code to get the path from an access table. Table Name= (tblExportsImports) Field Name = (DailyReport_Export)

Example: Set xlWb = .Workbooks.Open (tblExportsImports.DailyReport_Export)

Code:

Private Sub cmdSummaryReport_Click()
DoCmd.SetWarnings False

basChart.BuildChartData
DoCmd.SetWarnings False
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook

Set xlApp = New Excel.Application

With xlApp
.Visible = True

Set xlWb = .Workbooks.Open("\\rampbess004\GroupV\OPS\RelMaint TAR\TAR GoC\GoC TAR's\TAR GoC-2019\FS2\Daily Report\Daily Report.xlsm", , False)
xlWb.RefreshAll
DoEvents
xlWb.SaveAs FileName:="\\rampbess004\GroupV\OPS\RelMaint TAR\TAR GoC\GoC TAR's\TAR GoC-2019\FS2\Daily Report\ " & "FS2 Daily Report" & " " & Format(Now, "MM-dd-yy H.MMAMPM") & ".xlsm", FileFormat:=52
End With

DoCmd.SetWarnings True
End Sub

 

[xenou]

Access tables don't have paths. You can google for examples on importing Access data to Excel by googling "excel vba import access data"


Examples (no vba here but it is a very easy way to do it and I think import steps can be saved so one click and done):
https://www.excel-easy.com/examples/import-access-data.html
https://datatofish.com/import-data-access-one-click/
https://support.office.com/en-ie/ar...database-a3d6500c-4bec-40ce-8cdf-fb4edb723525


Coded solution:
https://www.excelanytime.com/excel/...rt-export-data-from-access-to-excel-using-ado

 

[bama4954]

I have an access table that I store the path in. \\rampbess004\GroupV\OPS\RelMaint TAR\TAR GoC\GoC TAR's\TAR GoC-2019\FS2\Daily Report\Daily Report.xlsm\
I want to be able to update the path in the table instead of having to go to the VBA to change it every time I need the path updated.

 

[xenou]

Hard to answer in with so few details. In general you should google for updating data in Access using VBA (from Excel, I guess, unless you are completely working in Access).

What would you want to update it to? How often? How many different paths are in the table? How would you know you are updating the correct one?

 

[bama4954]

I have an excel workbook in multiple folders. I have a link set up to update the excel workbook from Access. So when I run the code it updates the excel workbook from the path in the VBA. I have many different jobs that I need it to update, so I have to change the folder path in VBA every time I need to update a different folder. I want it to go off a primary key to look up the path for each different job then pick the path located in an access table by the primary key of that path. It works fine now, but I have to keep updating the path manually in VBA.

 

[xenou]

If your code is running in Access you can use DLookup:

Dim strPath As String
strPath = DLookup("MyPath", "Table1", "[ID] = 1")
If strPath = "" Then
    '//Error - path not found
Else
    '//do stuff
End If

If your code is not in Access then as above you have to google for some examples on how to access data in Access from Excel.

 

[bama4954]

I finally figured it out! Your code help me out greatly! Thanks!!