Basically what I am trying to accomplish is uploading multiples file automatically into server. The code I am trying to use works great if the user wants to upload a single file manually because the code prompts you to choose the file and I am wondering if there is a way to tweak the code. here is the code I am using
Code:
Private Sub Form_Load()
Dim objFTP As FTP
Dim strfile As String
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code: Loop through files in folder folderspec = "O:QA FilesQC ReportingPending Review" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.files
I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.
At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:
Function GetAllFiles() Dim fd As Object Dim strFilter As String Dim lngItems As Long
Const msoFileDialogOpen As Long = 3 Const msoFileDialogViewDetails As Long = 2
I am trying to build a newer database 2010, based on an older one,2000, that has been locked tight and I cannot see the modules to kinda get a reference of where to start. I am trying to find a VBA code that will allow me to import a several text files to one table. The text files are all in the same format but I cannot remove the page headers and footers to get the table to look right. I have attached an example of the text file i am trying to import but it is a stripped down version for information protection.
Also, it appears in the old Database Table once imported as:
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
If I could import the text files and end up with a table like this, it would be all i need as i could run all the queries i need from this.
I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code> Private Sub txtloan1_AfterUpdate() If IsNull(DLookup("[loan1]", _ "settlement", _ "[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then Cancel = True MsgBox "Test", vbOKOnly, "Warning" End If End Sub </code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
My report has tens of pages and I need to save each page as separate file with ID as filename. PDF prints with ID but all in one file. I use this code.
Option Compare Database Private Sub tisk() Dim cesta As String Dim kod As String Dim Sql As String Dim rs As Recordset
I have a few hundred access databases all with the same tables and structure, and want to import all the data in a 'mother' database (which i created with the same tables and structure).
I am using a sub that feeds a string to another public sub as follows:
Code: Private Sub cmdImport_Click() Dim strFolder As String 'source folder path for import Dim strFile As String 'individual file path strFolder = GetFolder(Environ$("USERPROFILE") & "Documenti") If Len(strFolder) <= 1 Then Exit Sub
I have parameter form that ask for state and begining date. on click it runs a query and creates a report by state that has multiple pages.. I want to create a separate pdf file for is page (which would be by delegate_name).
This is the coding I have so far and am gettting the error '424' object required. This is the coding I currently have. I haven't worked with access in over 9 yrs so am a little rusty with it.
StateAbb = Me.State PeriodStartDate = Format(Me.Period_start_date, "MM-DD-YYYY") Dim strSQL As String Dim db As Database Dim rs As Recordset Dim strRptName As String
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 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.
Body (pre filled with "Dear ([Contact]if possible) Please find enclosed your documentation for your recent telephone order bla bla bla and our logo.jpg if possible)
then i would like to attach to the email the cover letter/invoice(report), book page(report) and the book (pdf) from the hard drive (r.eports changed to pdf)
if i can press a button on the form to send it that would be good.
if i could link it to a query to send a batch of them that would excellent.
The Database is Named: b4s.mdb The table is called: Main Table The input form is called: Client The letter/invoice report is called: b4sletinv The Book page report: b4sbook The pdf book is located at c:Documents and SettingsAdministrator4sfinal.pdf The query for the batch is called: coverlet&proforma
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"
At this point, FE db has 4 linked tables, 3 are located in FileBE1, and the 4th is located in FileBE2.
The production BE is located on a server, the test (when working remotely) is on a local computer that is not able to connect to the server. Also, the test BE files (but not the table names) have different names than the production.
I have the idea of creating a table in the Front End that contains a list of users, filename and location (server or local drive).
Upon startup, how could I set up the vba to check the links and if they need to be changed will go and change them without the user having to click on link table manager or be prompted for names and path.
For now, I envision three rows in the table, one for the test user, one for local user and a default UNC for everyone else. (or would it be six rows since there are two files that need link checking and relinking).
Does anyone out there know how I might do the following:
I have a main folder which contains many sub-folders. In turen, each subfolder contains several files. I am interested in creating a text file that within each sub-folder will list certain files in that sub-folder. Example:
Read Main Folder Do While Sub-folders exist Do while selected sub-folder contains files If selected sub-folder contians FileZ then Write selected sub-folder name to text file Write time/date stamp to text file else loop to next ub-folder end if end do while end do while Close input and output
I have to loop thru 2 recordsets.I need to first determine the quarter , then retrieve a value from each of the 6 fields for that quarter and compare that value against a previous years value that is also stored in the table for each quarter.the following is the field names not the field value.
So I need to be able to set the value of PrYR = .385 and compare against the value of the 1st qtr for each tier for Qtr1.Then go to Qtr2 and repeat the process but grap the Q2 PRYR value = 1.25 and compare against all Tiers for Qtr2.As so forth for each quarter.then I need to compare the value of the PrYr and if it is the following then
If PctYrlyIncrease< Tier1 Pct (Q1T1) Payout = 0 elseIf PctYrlyIncrease> Tier1 Pct (Q1T1) and < Q1T2 then Sum(TotalNetUSExp * T1E) ElseIf PctYrlyIncrease> Tier2 Pct (Q1T2) and < Q1T3 then Sum( TotalNetUSExp * T2E)
and repeat for each Tier per Each Qtr.Here is my code so far:
Code: Public Function BkOvrCalc(ByVal gContractID As String) As Long Dim curDB As DAO.Database Dim strSQL As String, strSQL1 As String
Private Sub Form_Load() Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("select * from tblpatient, dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then rs.MoveFirst Do While Not rs.EOF ' do stuff rs.MoveNext Loop rs.Close Set rs = Nothing End If End Sub
I have watched it step through on debug and it does everything right for the first record, but it seems to come back to the same record.
I have a form where a textbox has shows the NameNo from the NameNo column in a db. At the moment I am using a dlookup to set the textbox = to the NameNo, but obviously this is only returning the 1st value in the column. How to get this form to automatically display each NameNo, creating a pdf of the form and then moving on tto the next nameno. I have the code to make the pdf working so far, all I need now is to figure out how to use the textbox to loop through all the NameNo's in the table.
i have the following code to get file list from specified directory:
Code: Dim db As DAO.Database Dim TB1 As DAO.Recordset Dim p As String, x As Variant
[Code].....
the field "File_Name " is not set to accept duplicate the above code work fine , but when it find the file name exist on the TB1 it return an error i need the code if find any error continue to retrive other files on the folder not exit loop and also i don't want to make check if file exist it will lower the speed of code
I work for an insurance company where various (and multiple) discounts or loads can be applied to a quoted premium for one reason or another.
These discounts/loads are stored in a table with a corresponding customer ID, where each row represents an individual discount/load (labelled and ordered-by a 'Step').
Unfortunately, the resulting premium from these discounts/loads is not stored in the database and are calculated on the fly by the front-end.
I have a requirement however, to store the 'new' premiums based on the stored discounts/loads for a report.
My problem is that the calculations must occur incrementally one after another, where the discount/load at each 'Step' applies to the resulting premium from the previous calculation.
The attached spreadsheet is demonstrative of the existing table where two additional columns have been added to show you what I need to calculate. 'Price_Amount' represents the discount/load in monetry terms relative to the calculation and 'New_Prem' is the premium resulting from the calculation step.
Notice that each discount/load applies to the previously calculated premium rather than the original one (Original_Prem = the starting point from which all further calculations should apply).
The full table would include many more rows for different customer ID's, where the number of 'steps' could be as few as 1 or as many as 7 per customer ID (ordered by 'Step').
Having toiled for many hours in Access to achieve the above, I am now resigned to the fact that only some kind of VBA function will achieve my requirements.
This function should take the Original Premium for each CustomerID and loop through each 'Step' applying the relative discount/load based on the value in 'Pricing' and the number type in 'Price_Type'. Once the function has calculated each step per CustomerID and has reached the maximum 'Step', it should move on to the next CustomerID.
such can only articulate the requirements without being able to convert this into actual coded logic. Nonetheless, I am in a bit of a pickle with this one and am under some pressure to create the report.
I am using to select and append data to an output table. Everything is working fine until the last step. The last step appends all the data to an output table from the initial table. The intermediate steps select the data to output.
"AddExpenseToOutput()" is stopping about halfway through the list of criteria. Initially I thought there might be a null in the list, but it doesn't look like there is after dropping everything into Excel to manipulate.
Code: Option Explicit Dim rstAccount As DAO.Recordset Dim rstAccounts As DAO.Recordset Dim CAccounts As String Dim Item As String Dim AccountKey As String