Modules & VBA :: Determine Excel File Type Without Opening The File?

Aug 14, 2015

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'm using Office 2010.

View Replies


ADVERTISEMENT

Modules & VBA :: Determine File Format Without Opening Database (Using Access 2007)

Jul 15, 2014

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:

Code:
Debug.Print db.Properties("Version").Value
Debug.Print db.Properties("AccessVersion").Value

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.

View 5 Replies View Related

Modules & VBA :: Determine If A Table Def Is Linked To Excel File?

Aug 8, 2013

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?

View 8 Replies View Related

Modules & VBA :: Opening Excel File From Access Form

Sep 21, 2014

I am trying to program a button on my ms access form to open up an excel file.. So far the simplest code ive found online was from URL....

<code>

Private Sub Command57_Click()
Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open "C:Excel1.xls"
xlTmp.Visible = True

[code]...

However the code doesn't work, any way to open a file from within access?

View 1 Replies View Related

Modules & VBA :: Creating Opening And Put Excel File In Folder

Oct 21, 2013

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

[code]....

View 2 Replies View Related

Modules & VBA :: Opening Excel File Errors Second Time

May 13, 2014

I have the following code, which works the way I want it to when I click the button the first time. However, if I close out the excel and click the button again, it errors at the line "ActiveWorkbook.Worksheets("Ref").UnProtect", with the message "Run-time error '91', Object variable or With block variable not set". I can not figure this out, why in the world does it work the first time, but not the second???

Private Sub cmdReport_Click()
Dim path As String
Dim XL As Object
Dim oBook As Excel.Workbook
Dim pic As Excel.Shape
Set XL = CreateObject("Excel.Application")

[code]...

View 5 Replies View Related

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

View 5 Replies View Related

Type Mismatch When Importing Excel File

Nov 19, 2013

I am getting the message (Type mismatch) every time I try to import a spreadsheet regardless if is to add into a existing table or to create a new table,

View 9 Replies View Related

Opening An Excel File In Access 97

Aug 26, 2005

I created an Excel file using:

DoCmd.OutputTo acOutputQuery, _
"Compile_Query", acFormatXLS, _
"G:ServiceCompanySanDiegoWarehouseLCDLine_Summa riesDaily" _
& DateMonth & DateDay & DateYear & ".XLS", False

However, after creating this excel file, i want it to automatically open.

Is there any way to do this in 97?

View 1 Replies View Related

Exporting Data To Excel And Opening The File?

Mar 9, 2005

Hi guys, this might be a quickie, I did a quick search but couldn't find anything :/

Basically my database creates a table which some people would rather analyse within excel because they're not comfortable with access. I can get the table exporting to an xls file no problem, howeevr what I would like is for the database to export the file and open the file in excel at the same time so the user doesn't need to open up excel and find the file etc...

Any ideas??

View 2 Replies View Related

Opening A Password Protected Excel File From A Command Button In Access

Dec 22, 2006

Hi All,
I have three excel files (ActualHires.xls, ActualPromotions.xls and
ActualSeparation.xls). These are password protected files (with the
same password). They are linked to an access database and whenever the
files are opened, one must supply the password and click the 'Enable
automatic refresh button.' What I was wondering was if this could be
done in access with a command button. I have pasted some code below
that I found but now I'm getting an error.
This is the code:

Dim BookNames As Variant
Dim B As Long


BookNames = Array("O:ExcelFilesActualHires.xls",
"O:ExcelFilesActualPromotions.xls",
"O:ExcelFilesActualSeparations.xls")


For B = LBound(BookNames) To UBound(BookNames)
WorkBooks.Open FileName:=BookNames(B), _
UpdateLinks:=3, Password:="*******"
WorkBooks(B).Close SaveChanges:=False
Next B


But when I click the button, I get this error:
Run-time error '9' Subscript out of range.


and this line is highlighted:
WorkBooks(B).Close SaveChanges:=False


Any help would be greatly appreciated.


Thanks.

View 1 Replies View Related

Forms :: Storing File Location And Opening File From A Form

Dec 5, 2014

i had a database that allowed me from a form to store a file location for a record on a form, so i any time i could access the record, and then open the file, usually a pdf, it wasnt stored as a hyperlink, but as long text

i made the mistake of not copying the files for safe keeping when i got a permanent position..i am now back self employed, i need to recreate the database.

View 4 Replies View Related

Modules & VBA :: Opening PDF File In Form?

Jul 22, 2015

I've got a table with field define as hyperlink which stores link to a external pdf.

Then i create a form including this field and add a unbound field which has got intermet browser format (as a type).

What code I have to put in onclick event in a hyperlink property sheet to open PDF inside the form?

View 1 Replies View Related

Modules & VBA :: Opening File Using Shell / CPAU

May 27, 2015

I want to open files from a networklocation from VBA, only normal users don't have access to that location (and should preferably not get it).

I instead of using shell("explorer.exe filepath") to open files using windows standard app for the file, which needs userpermissions to the path the file is located, for the currently logged in user.

I'm trying to use CPAU in combination with Shell, because using CPAU you can pass a user/password that has permission to open a process

The only problem is that the filepaths have spaces in them and that is Always a bit of a tricky thing to tackle.

So, for example, this works:

shell("N:GuidelineOntwCPAU -u DomainUser -p password -LWOP -ex " &
Chr(34) & "C:windowsExplorer.exe c: emp est.pdf")

But I cannot get it to work with a file that is on a path with a space in it (or with a space in the name), like

Code : N:EngineeringJB-MATBBBB-004_Panel PinBB-004_Panel Pin.pdf

I'm trying all kinds of thinks:

- adding chr(34) at certain places
- using """
- the above in different versions

But to this point without result.

Trying to figure out the plain cmdline:

Code:
N:GuidelineOntw10_Ontwcpau -u Domain/User -p Password -ex "c:windowsExplorer.exe "c: emp est.pdf""

Doesn't work:

Code:
N:GuidelineOntw10_Ontwcpau -u Domain/User -p Password -ex "c:windowsExplorer.exe "c: emp est 1.pdf""

(difference in filename with a space in it)

The last command start explorer, but doesn't open the file (just as the first line I posted, it opens Explorer, but not the file. Must be something with the way I pass the filename?)

Code : explorer.exe "C: emp est 1.pdf"

Also works in commandline...

View 4 Replies View Related

Text File To MS Excel Multiple File Import Application

Jul 20, 2006

hello,

I would like to automate something presently done on a one-by-one basis. Here: a number of text files(containing data) are to be exported into an MS Excel file, with each text file to occupy a different worksheet. Presently, the idea is to use the Data/import external data/import data feature of MS Excel for importing the text files one-by-one into newly created worksheets(within the same workbook).

I would appreciate some advice on how to go about creating a useful MS Access application to achieve the above. I have checked the available Macros in MS Access, but I could not find one to suit my purpose. can anyone pls assist, on how I can get started?

Tokunbo

View 3 Replies View Related

Modules & VBA :: Opening File Based On Directory Location

Feb 15, 2015

When creating an incoming inspection database, I want to set it up so when I am in a form to add data, I want to have a command button where I can pull up the associated CAD drawing based on part number and drawing number. I would like to enter the directory location into a table so I could change it if I need to.

So basically I want to go something like this:

.tblDirctoryLocation
txtDirectory

.tblProduct
txtProductName
txtProductNumber
txtProductCADNo

These tables would not be related to one another as there will only be one record for the directory location and multiple records for products.

Somehow I want the db to assemble file location like this:

“EQuality_DirectoryIncoming” & “ProductNumber_” & “CADDrawing” & “.pdf”
End result: EQuality_DirectoryIncomingProductNumber_CADDraw ing.pdf

Is there any way this could be done?

View 1 Replies View Related

Modules & VBA :: Removing First 6 Lines From XLS File And Save It Without Opening

May 13, 2014

I would need removing the lines from an .xls file(stored in shared folder) and saving the same. I used the below code and not successful in removing and saving the file stored in the shared area..When I execute the code, Cursor hangs at this line

Set objWB = objXL.Workbooks.Open(filename)

Code:

Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Open(filename)
Set objWS = objWB.Worksheets("Sheet1")
objWS.Rows("1:6").Delete

[code]...

View 3 Replies View Related

Modules & VBA :: Exporting A Form To Excel File

Mar 4, 2015

i have a form and i want to export it to excel file the form will be updated someties and the data will be changed here is my code, but there is a problem with it

Code:

Private Sub Command0_Click()
Dim xlApp As Object
Dim xlBook As Object
Dim rs As ADODB.Recordset
Dim sql As String
Dim i As Integer
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command

[code]....

View 1 Replies View Related

Modules & VBA :: Copy Paste Excel File From Access

Mar 31, 2015

All I need to do is copy an excel file from a location (while coping the file, the location of the source file remains same all the time)and paste that excel file where ever I want (Browser Option) from an Access VBA.

View 4 Replies View Related

Modules & VBA :: Exporting To Excel Template And Saving As New File

Jun 25, 2014

Any definitive way of exporting a query to an Excel file and then saving it as a new file without saving over the original.

I've tried to remove any confidential info from the code below so it's not exactly the same.

Code:
Dim XLApp As Excel.Application
Dim XLSheet As Excel.Worksheet
Dim tmpRS As DAO.Recordset
Dim strFolder as String
strFolder = ("C:Profiles"& [Name] & "")

[Code] ....

The error seems to be with the SQL statement although that may just be the first error that it got to. I read that you can't refer to a Query if it has a criteria and that you have to write the SQL directly into the code.

View 13 Replies View Related

Modules & VBA :: Get Last Updated Value Of Excel File In A Field In Access

Mar 18, 2014

Some vba code which is able to get the last updated or last modified value of an excel file stored in a folder in a shared drive and update a field on a form in ms access.

View 2 Replies View Related

Modules & VBA :: Appending To Existing Excel File From Recordset

Apr 18, 2014

I'm spinning my wheels on this. Trying to copy a recordset into an existing excel file.

Private Sub cmdExport_Click()

Dim conn As ADODB.Connection
Dim conn2 As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strConnection2 As String
Dim wks As Excel.Worksheet

[code]...

View 2 Replies View Related

Modules & VBA :: Detecting Excel File Is Password Protected

Apr 4, 2014

Any VBA code that would tell me whether an xlsb file is password protected?

View 5 Replies View Related

Modules & VBA :: Making Already Opened Excel File Invisible

Jan 14, 2014

I have the following code. In the Else part of the code, after the wb.Activate line, what code can I place here that will assign wb.Name to the objExcelApp object ao that when the line .Visible = False kicks in, it will make the already opened MS Excel file invisible.

Code:
Dim strExcelFile As String
Dim wb As Workbook
Set objExcelApp = GetObject(, "Excel.Application")
If objExcelApp Is Nothing Then
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Workbooks.Open FileName:=strExcelFile

[code]...

View 2 Replies View Related

Modules & VBA :: How To Make Excel File And Export It To Mail

Aug 18, 2015

i want to make an excel file from a query and then export it to mail. i want to make an excel and then send it as an attachhment. also i want to insert the mail address to manually because each time it is sent to a different person.

View 2 Replies View Related

Modules & VBA :: Closing Already Open Excel File From Access

May 25, 2015

I want to delete an excel file from within access vba. Problem is that if the file is open, it obviously cannot be deleted. so I need to check to see if that specific excel workbook is open and if it is, I want to close it before I can delete it.

All of the answers here involve creating an excel object and opening it before closing it. If I do that all that happens is a second instance of the workbook opens then closes leaving the originally open workbook still open.

Sub xx()
Dim XLapp As New Excel.Application
Dim ObjXL As Excel.Workbook
Set ObjXL = XLapp.Workbooks.Open("C:dropboxexcelimport.xlsx ")
ObjXL.Application.Visible = True

[Code] .....

The above code demonstrates my problem, if the import.xlsx file is already open then the code just opens another instance of this workbook and then closes it again leaving the original workbook open and as such cannot be deleted.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved