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?
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.
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?
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]....
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]...
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.
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?
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.
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 RelatedSome 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 RelatedI 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.
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??
I've vba in Report onformat the vba code does some data copying to External Excel file (premade by vba).Now on first run, i got success.But on opening the excel file, it showed blank space + Error"File already opened"
No White Spreadsheet is shown with data to see into file, i created new excel file in windows, and inserted the vba created/exported file as an Obj.Now Obj is showing correct and full data with spreadsheet as normal view.
I have an access program that is stored on a SharePoint site. I want to be able to export report to the computer even if I just open the access program as read only. Here's my code:
Code:
Private Sub btnExport_Click()
On Error GoTo btnExport_Click_Err
Dim db As DAO.Database
Dim strSQL As String
Dim qdfTemp As DAO.QueryDef
Dim qryFilter As DAO.QueryDef
[code]...
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]....
I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.Then I am linking the file to the database and subsequently doing a lookup on the date time on the access form against this table.The excel file when formatted from vba in access shows the wrong number
datetime = 03/05/2013 11:26
database number = 41397.4763888889
Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)
now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.Also I have tested the code with manually editing the excel file and this works fine.
Code:
Private Sub Command288_Click()
Dim s As String
Dim t As Integer
Dim ws As Worksheet
s = LaunchCD(Me)
MsgBox (s)
[code]....
also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows.
Code:
Private Sub LoadData_Click()
Dim w As String
w = Forms![frmList1]![Combo0]
w = "[" & w & "]"
Dim dtA As String
[code]....
I need some simple code that will copy an Excel file or a table in Access to a specific location on an FTP server. I would think this would be a very simple task, but I have yet to find any sample code that is *simple*. I have seen lots of code that requires downloading this dll or that mda, but the examples don't work. There must be something built into MS Access 2010 that will allow a file to be uploaded to an FTP site.
All the variables are known:
The FTP location (it never changes)
The FTP Username and Password (they never change)
The destination folder on the FTP site (it never changes)
The File type (it never changes)
The File name (available from the form in Access from which this will be executed)
I can either produce an output file, then copy it to the FTP site, or I can export the table directly to the FTP site with the file name for that day.
This seems to be a very simple task with no simple solution. Currently I am using an FTP app to get the file to the FTP site, but I would like to automate this. The process that creates the output file is already automated, so I would just like to add this to the existing code as its own module.
I'm looking for a simple command on the idea of:
DoCmd.FTPFile, acExel, TableName, FTPFilePath, FTPFileName, Username, Password, acUpload
just made all that above up and none of it is a real function/command in VBA, but is just the kind of thing I'm looking for.
I would think that since I can download and XML file from an FTP site that it should be child's play to upload a simple file to an FTP site, but I can't figure it out.
I have a form which i use for a user to select an excel file they want to import and then click a cmd button to import the file into a table which works fine, however i want to append a date into a date field from an unbound txtbx before the file is imported so it will look something like;
id;date;excel info;excel info;excel info.
What I would like to do is create a form that lets the user browse for an excel file, then will click an "import" button which then imports all the fields in excel into a table.
View 1 Replies View Relatedi 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.
I have a query, which looks like that:
ID Year SAP Geris
1 2008 20,00 € 5,00 €
1 2009 40,00 € 4,00 €
1 2010 60,00 € 6,00 €
2 2007 80,00 € 4,00 €
2 2008 100,00 € 8,00 €
2 2009 100,00 € 4,00 €
3 2008 1.000,00 € 1,00 €
3 2009 100,00 € 8,00 €
3 2010 2,00 € 9,00 €
4 2008 9,00 € 10,00€
So each combination has an ID. It's called the SuWID. I want to Transfer the data to a fixed Excel sheet. I wrote the following code
Dim xlApp As Object ' Excel.Application
Dim xlBook As Object ' Excel.Workbook
Dim xlSheet As Object ' Excel.Worksheet
Dim rst As DAO.Recordset
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
[Code] ....
The only Thing what i still want to have is that, before it opens the Excel form, which works already perfectly well, that a msgbox will Show up and ask me, which SuWID do you want to see in the Excel sheet.
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...
Hi All
I'm not sure if this is even possible but I have a table which has a list of Categories(text) and another field named filepath(also text) which contains a file path which points to specific pdfs. So my question is Can I create a report which then prints the categories with a button next to them which if clicked on opens the coresponding pdf ?
Any help will really be apreciated I am a database novice but am really good at following instructions and copying code :)
Thanks in advance
Mel
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?
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]...
I have set up a command button to export an query to Excel, then create a pivot table with the data. That all works fine however the code which opens the sheet for veiwing after the code formats the pivot table only works once. The second time the user runs the query it is in read only mode those it has been close. The code I used to open the spreadsheet is:
Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
On Error Resume Next
oApp.UserControl = True
Workbooks.Open Filename:="I:Program FilesCount.xls"
The users are just closing excel normally. Any Ideas?