Modules & VBA :: Determine If File Exists In Sub Folder
Dec 15, 2014
How to search for a file using the dir function, however, i need to search for a file from a main folder which may have several sub folders.
I am having no luck so far - i understand there is a recursive search facility but im not sure how i can get this to work with what i am trying to achieve.
This is the code i have for the dir function:
If Len(Dir("as-tamworth-50midlands.qa$TamworthLaminate C of CsCircuit Foil" & CofC & ".")) = 0 Then
MsgBox "This file does not exist"
Else
MsgBox "Yippee"
End If
Question: Is it possible, using VBA, to determine the actual Excel file type without opening the file?
I receive data files from other departments. Seems like every time someone changes their download structure, I end up with file types that do not match the download extension (example: xlm file with a xls extension). The files can't even be opened because of this. I think I can fix it if I could figure out how to determine what the file type really is.
I am writing code to Refresh table links. I only want to refresh the Access table links and ignore the Excel Table links. Is there a way to determine in VBA if the table link is pointing to an Excel file?
I'm trying to view a pdf in a form if the file exists. My search has brought me to trying the WebBrowser control. I have it loading the file but it launches Adobe instead of just showing the PDF in the window on the form.
I have code working for some workers here ,who used to save excel files in a folder and my databse upload those files into database and then do the rest.I am looking for a code in vba which,on upload button matches the file in table and if the file already exists ,it should not upload again in the table and if it does not exists it should upload . I have attached my code below :
Private Sub Command0_Click() Dim strcPath As String strcPath = "O:QA FilesQC ReportingPending Review" Dim strcNewPath As String
Using Access 2007, can I return a value for CurrentProject.FileFormat WITHOUT opening the database?
I'm looking for a way to determine the file version of a given Access database without actually "opening" the database in Access (I don't want it to ever be visible at all or in any way).
So to open a database in Access 2007 "without opening it," I'm using:
Code: Dim db As DAO.Database Set db = DBEngine.Workspaces(0).OpenDatabase("C:Path-ToFile.accdb")
From here, there are at least two different "version" indicators that can be used, as in this example:
Now, if you run this code on an Access 2002/2003 .MDB file, you will get:
Code: 4.0 09.50
If you run this code on an Access 2007 .ACCDB file, you get:
Code: 12.0 09.50
Note two important factors:The db.Properties("Version") seems to be returning the JET version from what I can see in research. This is NOT what I'm after.
The db.Properties("AccessVersion") returns THE SAME VALUE for an '07 ACCDB as it does for an '02-'03 MDB, since Microsoft never updated it, apparently.
Now.... the one way that I have found that ACCURATELY describes the version of a given access database file is to use:
Code: Debug.Print CurrentProject.FileFormat
...as this will always return, for example, 2.0 for an Access 2.0 .MDB file; 10 for an Access 2002/03 .MDB file; and 12 for and Access 2007 .ACCDB file (and there are others; these are AcFileFormat bitmask constants as described in Access support).
BUT, this CurrentProject object doesn't seem to be available (so far as I can see) when using the "open without opening" method described above.
SO, is there any way to return the FileFormat value without opening the database visibly? (Merely basing it in the extension isn't right either.... that would be easy but not accurate as several formats use MDB.
Actually what I am looking to do is have an Access application check some Web site for the presence of a number. This number could be the Serial number of the application and if this number exists to maybe show a message in the aplication or even terminate the application.
A quick and dirty way is for User Serial number 254, to put on a specific page of a Web site say arbitrary file 254.jpg (invisible on the Web site). The application would check for the existence of this file and if it is there, it could take some action. Everything would have to be done in stealth mode.
The following code can tell me if a specific URL exists, in this case [URL]
Code:
Private Sub cmdArbitray_Click() Dim blnDum As Boolean blnDum = blnCheckURL("http://www.VisualDentist.com") ' If blnDum = True then it exists End Sub Public Function blnCheckURL(ByVal strURL As String) As Boolean Const FLAG_ICC_FORCE_CONNECTION As Long = &H1 blnCheckURL = (InternetCheckConnection(strURL, FLAG_ICC_FORCE_CONNECTION, 0&) <> 0&) End Function
I'm trying to write a function to unzip a file (working off Ron de Bruin's code here)..The function should take the path of the zipped file as an argument and return the path of the unzipped file (extracting to the same folder)
Code: Public Function UnZipFile(strFile As String) As String On Error GoTo ErrorHandler Dim objFSO As Object Dim objShellApp As Object Dim strUnzipped As String
[code]...
But the code has no effect (i.e., I'm still left with just the zipped file in the folder, not the unzipped contents?) strFile is a legitimate path to an existing .zip file which contains a zipped .xls file
...but found that the .Count property was 0?I've done several searches on how to unzip files using VBA and this seems to be the universally accepted method.
I have a macro in access that will create a folder and create a word doc in the folder with the name of the folder. I would like to add to the same folder an excel file. The excel file is in a folder and needs to be copied every time in a new folder when created. where to add the excel file from the below folder:
C:Excel CopyCombine_PDF.xls
this is the macro:
Code:
Private Sub Command22_Click() 'this will register the letter and create folder, word doc and open all Me.[DateRegistered] = Date Me.[PersonRegister] = Environ("Username") Me.Refresh
I am trying to display an open file dialog window so that the user can pick up a file. I wish the window to show a specific folder. How can I do this? The code I am using is below. The parameter InitialFileName has no effect on the outcome.
Code: Function GetFileName(strPath As String, imtype As String) As String On Error GoTo Err_GetFileName Dim Dlg As FileDialog Dim sfl As FileDialogSelectedItems Dim sflitem As Variant Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
I am using MS Access 2010. I have a code that I am using that loops through a folder gather all the names of the files in the folder and inserts them into a table (shown below). The table is named "tblFiles" there are two columns in the table titled file name and file path. What I want to do is to also loop through the same folder to find the file path of the files and insert them into the file path column has a hyperlink where users can just click on the hyperlink and the file opens.
Private Sub Form_Load() Dim fs, f, f1, fc, s Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("tblFiles")
I have the code below and I want it to open a file from my documents folder. The only problem is that every computers path is different to this folder. Any easy way to work around and open a file in My Documents without the full path?
I want to eliminate the part of the path in red and make it universal because computers will have a different number.
Operating System: Windows 7
Code below:
Public Function AddITARPicOffloadAnalysis() Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") With xlApp Dim wb As Object Dim ws As Object Dim Lastrow As Long
In VBA I have set a timer on a form to run a query and export to a specific folder as an excel file.
If I open the database as 'File Open' and open the form and let the timer run it exports perfectly.
As soon as I put the database into runtime - the Timer code kicks in and starts running but as soon as it hits the export line. It stops and then does nothing
I have a table with an attachment field called email, where a .msg file is stored for each record. Looking for code to save this contents of this field in a folder in my drive.
I'm currently working on a database to track and inventory documents in my organization similar to a library catalog, but with a digital archive as well. I'll need the ability to "attach" documents to records in the main inventory (though using links instead of storing it in the db) and have the documents live in a centrally accessible location on our shared server.
I will need to have this function work through a few different tables and in a few different iterations, but I'm trying to work on the simplest part first, which is attaching a thumbnail/preview of a given document to the record for that document.
I've got some VBA working well which opens the file picker and copies the file to a directory relative to the DB. It also creates a folder with each employee's ID number if it doesn't exist already (which it gets from the Windows log in) and adds "Copied_" to the front of the file.
Now I'm trying to get the VBA code to interact with the record. Presumably I need to tell the form button I'm using to launch the VBA code to pass the primary key of the record being displayed to VBA. Then, at the end of the VBA code I need to take that value and find the record again from VBA so I can update the MediaThumbnailLink in the original record.
Does this order of operations make sense? If so, how should I go about passing these values back and forth and writing the new link value?
Here's the VBA I'm using so far...
Code: Option Compare Database Public Sub GoCopyThumbnail() Dim fDialog As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) Dim varFile As Variant Dim LUser As String
I'm trying to open a folder based on a BIN nr. This folder could be in a main folder that has diferent subfolder. As there where differnt naming used to create the folderes, one of the things they have always is the BIN nr. It alwasy start with a unique number and maybe I could use it to scan the subfolders and open that one that the BIN nr is equal as in the field BIN.
Now we have serveral 1000th of folders and finding them takes time.
I have a table with several thousand records. Each record as a path field that indicates a specific file that is associated with a given record (not just a directory, it identifies a specific file). I'm pretty sure that most of them are correct but I've seen and been cleaning up paths that have had typos in them or were otherwise not setup correctly. Is there a way that I can generate a report or a list of some sort that would tell me exactly which file paths exist and which ones don't? or even just the ones that don't.
I am working with my back end on dropbox. and the frontends on other peoples Pcs, Is there away to run an if statement stating,
If (the back end Locked* file exists), then msgbox "Someone Is in the Database" Else docmd.OpenForm and so on.....(I know how to do this part
The reasoning is that it creates conflicting copies when you edit data from more then one Front End. and it is difficult to copy the conflicting data into the actual DB.
i'm trying to do something that i thought would be pretty simple. what happens is i have 2 files that get used, one by access and one by excel. they are both the same, except if one gets modified then the other doesnt obviously. (theres reasons for having the 2 files).
anyway. in access i want it to pick the newer of the two files (i assume this is using a fso system object?), if the one it normally doesnt use is newer, it should copy the new one over the old one and continue using the one it normally uses.
i cant simply use the other file as another user always has that file open.
any sample code on how to check if things are modifyed at a later date? and copy/pasting using those fso objects?
As you can see the picture naming system is standard and is based on the content in the type field. If one record of the Type field is say 12-6636 then the picture will be RC126636-01 (for Pic1) and RC126636-02.
Is there a way where the user say... can type C: and it will populate the picture file names in a combo box?
There are about 2000 records in the DB and I am just trying to find a way to keep a user from typing all that stuff.
:rolleyes: Could there be a way to do all 2000 records at once?...:rolleyes: Thanks.
I am new to Microsoft Access and have a little problem. I have searched the forum and nearly found the fix but I didnt quite understand what to do, so im creating a new thread.
I have a Access Database and have a client page that stores all the information about that client. We produce reports (scanned word/pdf) for each client and we store the report files in a client folder using the Client ID taken from Access.
My question is can I somehow produce a link from the Client Form in Access to the Client Folder or Files in explorer. I have seen the Hyperlink option but that is only availabe in Design View.
I am trying to set up a link to a file folder on the computer fore each individual, it is not going to the individual people, it is just going to the one person where I put the link to file.
I have a form with a subform.In the footer of the subform I have a combobox that get the file names of excel spreadsheets from a folder.I have a button (no code on it yet) that I want to press and import that spreadsheet into the table for the subform and associate it with the main form.
We have a server that we use to run a Microsoft Access application. This application creates folders, subfolders and files on the server.Users are in 4 different offices. Each office has at least 3 computers. Some users also work from home.
Users creates file through word, or scanning a document or by saving an email on their local pc.My problem is that users need to be able to save a file to a specific folder on server.
