Modules & VBA :: Saving Excel Files Through Access In Particular Directory
May 4, 2014
I am using Access to save an Excel file in a particular directory. The code was created about 10 years ago and hence saves the file with .xls extension and I would like to save with a .xlsm extension. If I just change the .xls to .xlsm, in the code below, the file is saved but the Compatibility Checker comes up (I would like to stop this) and when the file is reopened I get an error message stating there is a file extension problem and the file won't open.
come up with the below code to create PDF documents based on a query. It has been working great but I have a change that I need to make and can't figure it out. The code below creates all the documents in one folder "serverD$DocumentsInvoices" but I need it to create a separate folder for each PDF so that the file (Invoice1234.pdf) will be in the folder (serverD$DocumentsInvoicesInvoice1234Invoic e1234.pdf). That folder does not exist, so I need to create it and then save the PDF into it.
Code:
Dim db As DAO.Database Dim rst As DAO.Recordset Dim qdf As QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("Invoice_Report") Set rst = qdf.OpenRecordset
I have some code that resides in a button on a report that allows me to save a PDF version of this report. My problem is that if i have the destination directory open on windows explorer, i get a runtime 2501 "The output To action was cancelled" error.Is there any way to check if the directory is already open, and cancel the output to, or close the window to avoid this error?
Here is my code:
Code: Private Sub CmdSavePDF_Click() Dim MyFileName As String Dim MyPath As String 'Dim CompanyName As String
I'm using a function to find all files/folders in a specific directory, then copy each one to a specific destination folder. I'm going to use this on a weekly basis to backup files on a computer. I would like to use the vbarchive in GetAttr/SetAttr so I only need to copy these if they have changed since last backup.
Something like:
If GetAttr(strFile) And vbArchive = 32 then filecopy xxxx, xxxx else rs.movenext
As I understand it, a value of 32 indicates that the file has been modified since it was last backuped up (i.e. since the file attribute was "reset"). How do I "reset" the file attributes to clear the vbArchive?? I've read some google searches and the only thing I could find was the files were set to vbnormal however I'm concerned that this will erase program files that are vbReadOnly or VbHidden.
I have contract notes files in .xls format towards sale of equity shares from the brokerages. I have to do some editing in these files like deleting some rows, adjusting width of columns and inserting date of contract.
a) Contract-A.xls shows original file. b) Contract-B.xls shows modified file c) VBA macro code used for deleting rows and modifying column width is shown in modify_contractnotes.xlsm file. d) Presently I open the file and copy and paste the contents on the macro-enabled workbook sheet . After macro operation I transfer back the contents by copy and paste to a master file(Contract-C.xlsx) and clear the contents in the macro enabled workbook to make it ready for another operation. e) It is time consuming process for around 60 files per month. f)After this operation I run RDBMerge add-in to consolidate and normalize all these files in a directory. RDBMerge smoothened out merged cells also.
Is it possible to have a macro-enabled workbook open all the time and the contract files be opened one after other and macro contained in macro enabled workbook by modifying its code suitably operate on these contract files.
Files are attached in modify_contractnotes zipped folder.
create/find code that will count the number of text files in a directory with one level of folders please see below:
Directory path is : B:
First and only level of sub folders:
there are 100 of these and they all end in ".fof"These .fof folders will always contain .txt files.I've tried using the following code, but it always returns No files found.
Code:
Dim lngFileCount As Long Dim StrFileName As String StrFileName = Dir$("B:*.txt") Do While Len(StrFileName) <> 0
I have files that have extension of TSV which are text files but viewable in exel. I figured out a way for the user to click on a button in Access which does the following
1. Run Macro in Excel: The macro prompts the user to select the TSV file. After selection, macro opens the employee.tsv file in the excel (with excel being invisible) and saves it as employee.xls
Code: Sub SaveTSVtoXLS() Dim myPath As String Dim myString As Variant Application.DisplayAlerts = False With Application.FileDialog(msoFileDialogOpen)
[Code] .....
2. Imports the Excel file (employee.xls) into two tables: tblEmployee and tblDepartment using the following codes.
I'm starting to automatically save PDF copies of certain reports in predetermined locations. I need to copy a file to a particular directory (based upon various criteria), but if the directory does not exist, I want to create the directory on the fly first. Otherwise I will just proceed with the copy.
I'm sure there is a DOS command to do this, but I can't find it.
I have 200 excel files in folder C:UsersLburchDocumentsHistoric DataTedan Data in Excel Worksheets - CopyAAA - Copy.The files only have one worksheet and the column format are the same. Worsheet name is different for every file though.Headings in first row.write a macro to import into 1 Access table so I don't have to do it manually.
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.
I am trying to write some code to open an existing excel form, populate it, and save it with a custom filename in a directory that is built using variables I have declared.
The only part I am having difficulty with is setting the pathname. Here is my code:
Code: 'Build path to save file to spath = "S:ContractsGeomatics LABSigned" & Company 'Build File name Filename = "" & reservationNumberforForm & " " & Company & "-Payment Schedule"
'Save the payment schedule to the S: drive 'xlApp.ActiveWorkbook.SaveAs Filename:="S:ContractsGeomatics LABSigned" & Filename, FileFormat:=xlOpenXMLWorkbook xlApp.ActiveWorkbook.SaveAs Filename:="" & spath & " " & Filename, FileFormat:=xlOpenXMLWorkbook xlApp.ActiveWorkbook.Saved = True
I want to link into the active directory to get a list of staff usernames, so it will update the user table as new members of staff are created etc. We currently have around 130 staff, so rather than manually updating the user table i want it to pick up the usernames from AD?
I need to add two additional columns for each imported spreadsheet, which are not in the original files.
First column, named 'Customer Name', needs to retrieve information from cell A6 in Excel file, which is not part of range "name" that is imported to Access. If necessary, I could somehow include it in this range but then I still would have a problem of how to repeat a value of A6 in each row of table in Access.
Second column, named "File Name", I would like to include an Excel file name from which data was imported. Again, I would like to repeat value for each row in the table, i.e. if I have 6 rows with data from file X, I would like to repeat X.xlsx in each row in column "File Name".
Code: Dim strFile As String DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM Data;" ' Set file directory for files to be imported strPath = "C: est"
I have a requirement to create a piece of vba that will open all xlsx files in a folder one at a time then import the data in a range (sheet1!A1:G14) into a table named Weekly Input.
I have a database that, I would like to add a button that performs a active directory lookup. I would like it to check a username with Active Directory, and auto populate a few fields.
First Name Last Name Manager Department
This is my first database and I have very little exp using VBA.
I have a database which I've creating but now want to make others available to update - in the past this has not gone well! The database uses data from two excel sheets and the problem has been people do not always check if these have been updated today before running and therefore the database gets loaded with old data.I have found code that will check and display a message showing datelastmodified for a file but what i'm trying to find is code which will check both files' datelast modified, if they are modified today then run the update macro, if one or both are not modified today, then to display a message saying that the excel is not updated.
So I've just started with a company where my job is to manage a ton of data. I am new to Access (2010) and am completely impressed by all the awesome things it can do. However, I am running into some issues with knowing the best way to get my current data (which is all in Excel spreadsheets) into Access.
We are working with about 68 customers in 6 different focus areas (or areas of improvement). To make it easier for them, my company has not required them to enter all their data for each area into a centralized database. Instead, we are pulling reports (into Excel) from 3 different databases. The customers who are not currently submitting data to these databases are sending us Excel spreadsheets with their data (they send a seperate spreadsheet for each focus area). So you can see where my problem lies ... NOTHING is the in the same format. Even then the Excel spreadsheets look different from month to month (and customers submit a new, updated form every month with the current month and previous months) because the people managing the data before I came along changed the format a few different times and the customer has the ability to change them too.
Basically, I would really really really like to be able to just link the Excel files to my database, but I am struggling knowing how to do that with my data in so many different places and forms.
Im familiar with Ms Access, but have never used VBA or Scripts. I have 37 Excel files with the same data and would like to import into one file. Data will be received on a monthly basis into the same directory and I would like to automatically upload the data into the same file in Access.
some code I've come up to in order to import multiple excel files each of them with a different number of worksheets into an access table. The procedure is called from an Access database. The problem I have with the code is that when it encounters a workbook with only one worksheets (e.g. Sheet 1) it gives the error that "Sheet 2$" is not a valid name. When geting to a workbook with 2 sheets it says that "Sheet 3$" is not a valid name and so on and so forth. Is there a way to "check" the number of sheets in the workbooks and when it has only one sheet to transfer it and go to the next file?
Below is the code:
Code: Sub ImportExcelFiles() Dim strFile As String 'Filename's Dim strFileList() As String ' File Array Dim intFile As Integer 'Number of files Dim filename As String Dim path As String DoCmd.SetWarnings False path = "D:Tranzactii"
I have a data file I am importing into MS Access 2010. One of the fields is a large text field. When i import that field into Access the text is getting cut off. How do I get the full text field to import without cutting off?
I have a lot of Excel files and each of them has 3 sheets that I would like to import in Access 2010. How can I import them without having to do one by one?
I have some data in excel which I am importing it into MS Access 2002. Each excel file (one worksheet per file) is imported into separate tables.
I want to combine all my imported tables into one table. Is that possible, if yes then how and if no then what can be done to get single table after impoting data from various excel files.
