PDA

View Full Version : [SOLVED] Passing Variables through VBA Excel - VBA Access



bobdole22
09-10-2013, 02:06 PM
Is their a way to pass a variable through the two? I have an excel program that opens up Access and runs a macro in it. I really need a variable in that macro that I grab from Excel (such as a filepath). Any ideas? Is this even possible?

mrojas
09-10-2013, 04:02 PM
Have you tried converting the Access macro to code and run this within Excel, after properly connecting to database of course?

Zack Barresse
09-10-2013, 05:29 PM
Depending on what you are doing exactly, this can be fully automated from within Excel. I agree with mrojas in that you should do this with VBA, but from within Excel controlling Access. There are a hundred ways to skin this cat, but the solution depends on what you're doing exactly.

bobdole22
09-12-2013, 06:49 AM
Have you tried converting the Access macro to code and run this within Excel, after properly connecting to database of course?

Yes, that is what I want to do. Would you have any advice on turning an access macro into code?


And alright, I'll give you a description of what I am trying to do:

1) Open Excel.
2) Select a file and save it's file path to a variable.
3) Connect to Access.
4) Using that same variable file path, Import a text file (.csv).
5) Display the data on excel.



I have the program working, but right now I don't have #4. I just have radio buttons to select 1 of 4 files, and on Access they match accordingly. This method stinks because as a new file come out every month, we have to go in and add another radio button. At the moment I'm using the Access macro "TransferText," but I need to convert it to code.

mrojas
09-12-2013, 10:56 AM
I'll assume you're using Access 2007. Open your form in design mode and along the ribbon, on the right hand you should see a clickable icon labeled "Convert Form's Macros to Visual Basic"
As for the importing, I have code that does exactly what you need done. I'll have to clean it up a bit to be able to send it to you.
I'll do that sometime today.

bobdole22
09-12-2013, 11:03 AM
I'll assume you're using Access 2007. Open your form in design mode and along the ribbon, on the right hand you should see a clickable icon labeled "Convert Form's Macros to Visual Basic"
As for the importing, I have code that does exactly what you need done. I'll have to clean it up a bit to be able to send it to you.
I'll do that sometime today.

Wow, thanks! I didn't know about that feature and it seems like a lifesaver. All I need to figure out now is how to run that sub from excel. Right now I am using:

.DoCmd.RunMacro "Month - May"


Is it possible to do ".DoCmd.RunCode 'Month Finder'" or something like that?

Kenneth Hobs
09-12-2013, 11:13 AM
'Add Reference to Microsoft Access 14.0 Object Library
Sub Upload_PlannedHeadcount()
Dim axsApp As Access.Application
Set axsApp = New Access.Application
Dim strDatabasePath As String
Dim strUploadPathExcel As String
Dim strUploadTemplateLocation As String
Dim strUploadTemplateFile As String
Dim strDatabaseLocation As String
Dim strDatabaseFile As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
strUploadPathExcel = strUploadTemplateLocation & strUploadTemplateFile
strDatabasePath = strDatabaseLocation & strDatabaseFile
With axsApp
.OpenCurrentDatabase strDatabasePath, True
.DoCmd.TransferSpreadsheet acImport, , "tblData_PlannedHeadcount", strUploadPathExcel, True
.CloseCurrentDatabase
End With
End Sub

mrojas
09-12-2013, 11:24 AM
The attached code will allow you to "browse" for any file, anywhere on any drive. Once the user selects a file, you can do whatever it is you need to do with it. Remember, all this is happening "within" Access, and not in Excel.
I did as much cleaning up as possible, but there may still be some inconsistencies.


Private Sub cmdBrowse_Click()
strFileToImport = f_LocateFile("Select Agreement Document")
If Not IsNull(strFileToImport) Then
btnAgreementDocument.Enabled = True
Else
btnAgreementDocument.Enabled = False
End If
' Do with strFileToImport variable whatever it is you need to do
End Sub

Public Function f_LocateFile(TheTitle As String) As String
' Date: 12/21/00
' Author: Milton Rojas
' Purpose: Prompt user for file location
' Requirements: Microsoft Office XX.XX Object Library
'Declare a variable as a FileDialog object.
Dim dlgPickFile As FileDialog
Dim varFilePath As Variant
On Error GoTo TryError
'Create a FileDialog object as a Open File dialog box.
Set dlgPickFile = Application.FileDialog(msoFileDialogOpen)
With dlgPickFile
.Title = TheTitle
.AllowMultiSelect = False
If .Show = -1 Then ' User selected a file
' Store path of file selected to variable
For Each varFilePath In .SelectedItems
'varFilePath = dlgPickFile.InitialFileName
'gstrFileAndPath = varFilePath '& gstrFileName
f_LocateFile = varFilePath
Next
Else
varFilePath = ""
End If
End With
Exit Function
TryError:
MsgBox "f_LocateFile subroutine: " & vbCr & Err.Description, vbExclamation, "Error"
End Function


' Date: 5/1/00
' Calls: Windows API
' Last Update:5/18/00

Private Type ****EMID
cb As Long
abID As Byte
End Type

Private Type ITEMIDLIST
mkid As ****EMID
End Type

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib _
"shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pIDL As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib _
"shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Const BIF_RETURNONLYFSDIRS =

Public Function f_ChooseFolder(sTitle As String) As String
' Date: 5/17/01
' Purpose: Prompts the user to select a folder
' Called by: f_LocateFolder
' Last Update:5/17/On Error GoTo ChooseFolder_Err
Dim BI As BROWSEINFO
Dim IDL As ITEMIDLIST
Dim pIDL As Long
Dim lResult As Long
Dim ipos As Integer
Dim sPath As String
BI.hOwner = 0
BI.pidlRoot = 0&
BI.lpszTitle = sTitle
BI.ulFlags = BIF_RETURNONLYFSDIRS
pIDL& = SHBrowseForFolder(BI)
sPath = Space(1024)
lResult = SHGetPathFromIDList(ByVal pIDL, ByVal sPath)
If lResult = 1 Then 'a return value of 1 indicates success
ipos = InStr(sPath, Chr(0)) 'Trim string before last null char
f_ChooseFolder = Left(sPath, ipos - 1)
Else
f_ChooseFolder = ""
End If
ChooseFolder_Err:
Exit Function
End Function

bobdole22
09-12-2013, 12:20 PM
Thanks for the help guys! I will try out the codes asap.