I am trying to use transferspreadsheet to import access worksheet and then I want to append additional fields. I am able to import the excel sheet into access, but need appending the other fields.
Code:
Dim fd2 As FileDialog
Dim xlapp As New Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim db As DAO.Database
Dim tb2 As TableDef
Dim fdx, fld As DAO.Field
[code]...
I just want to run a series of DoCmd.TransferSpreadsheet commands to export several queries to one Excel Workbook with seperate sheet names for each query.
However my problem is I want this to be a new instance of Excel that the user will then SaveAs after it is complete. I don't want to save it to a specific path first because the Db is on a shared drive and My Documents will have a different path for each user based on their user name. (corporate environment)
There must be some way to just have it open a new workbook without saving isn't there??
From Here I want it to open the ".csv" that I chose (for some reason it doesn't now), Import to a table (I can do this part). Then export to the same location on the server with a different file name of my choosing and covert to .xlsx.
Dim fdlg As FileDialog: Set fdlg = Application.FileDialog(msoFileDialogFilePicker)
Do
With fdlg
.AllowMultiSelect = False
.Title = "Select File to Import"
.Filters.Clear
.Filters.Add "All Files", "*.*", 1
[Code] ....
DoCmd.TransferSpreadsheet Range parameter.
I want to import from an xls-File just the second page, titled "Harte". How do tell this the compiler?
Code is:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "Harte", file, True, "Harte"
I am extremely new to writing code, but was able to successfully write code that transfers the data in my form to and excel spreadsheet template and saves it with a different name. The code works perfect and without issue as long as Excel is not running. However if Excel is already open the template remains blank and a warning message appears saying that the form is locked for editing. Below is the code I used.
Private Sub ExcelClear_Click()
Dim xlApp As Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
[Code] .....
I know its not possible. But Just wanted to confirm. Not at all possible?
Code:
Function PreImport(BookToImport As Workbook)
For Each cell In BookToImport.Sheets(1).Rows(1).Cells
cell.Value = Trim(cell.Value)
Next cell
BookToImport.Sheets(1).UsedRange.Rows(1).Replace ".", "_"
End Function
In the code below I am creating a record with the INSET INTO statement and then Updating with additional info in various fields. it is not working the way I thought it would, so I am trying to create the record in it's entirety.
Code:
SQL_Grade_GUSD_ID = "INSERT INTO Grades (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"
SQLM1_1_ELA = "UPDATE Grades SET Grades.Subject = ""BM1(ELA)"""
SQLM1_2_ELA = "UPDATE Grades SET Grades.Type = ""Exam"""
SQLM1_3_ELA = "UPDATE Grades SET Grades.Score = ""0"""
SQLM1_4_ELA = "UPDATE Grades SET Grades.Nam = ""GUSD BM-1"""
DoCmd.RunSQL SQL
[Code] ...
I am running to syntax problems when I try to USE the INSERT INTO to create the record with all the info in one statement.
Code:
SQLM1_1_ELA = "INSERT INTO Grades ( GUSD_Student_ID, Subject, Type, Score, Nam ) " & _
"SELECT (" & Me.GUSD_Student_ID & ")"" AS GUSD_Student_ID, ""BM2(ELA)"" AS Subject, " & _
"""Exam"" AS Type, ""0"" AS Score, ""GUSD BM-1"" AS Nam " & _
"FROM Grades"
I am Getting this error:
Syntax error (missing operator) in query expression '(12345)" AS GUSD_STUDENT_ID,
"BM2(ELA)" As Subject, "Exam" AS Type, "0" As Score, "GUSD BM-1" AS Nam From Grades'
I have a database scheduled to run nightly. In this database I have a macro that runs a macro. I want one of the arguments to export "T_Update_Log" from the database to an excel file as part of it's nightly macro. When I use "Transfer Spreadsheet" in my macro, it creates a new worksheet in my excel file each night (for example: T_Update_Log1, T_Update_Log2, etc) Instead, I want it to overwrite existing one.
I have also tried "Export" command, but that throws a prompt asking if I want to overwrite the existing excel file. I cannot have any message prompts during my nightly task or else it will stop the whole process. Setting the Warnings to No does not stop this particular message prompt.
Thoughts? Thanks in advance!
This problem seems crazy to me.......
If I create a macro with a single entry consisting of Transferspreadsheet from a single Access table to Excel it exports perfectly. When I call Transferspreadsheet from VBA code it moves some of the data and puts them into seemingly random Excel spreadsheet columns why is this?
This issue first came about when we had to export a table with more than 65,000 records in and so I export using a table name "Sheet1" , "Sheet2" as needed but surely the table name that it comes from makes no odds?
Any help appreciated!
Regards,
Neil
I am using
DoCmd.TransferSpreadsheet acExport, , qName, wFilepath, True
to export a query to a spreadsheet. I am using this method as it seems to solve the problem of memo fields being truncated.
It seems however that using this method if the spreadsheet file already exists (and is of a differant structure) nothing happens and no error is thrown.
Is this correct? - Thanks Paul
Hi,
I´m using the open for append method to write to a text file which is then converted to an xml file.
I loop through some lines in excel and for each line I´m writing it to the text file.
The problem is every string begins with " in the xml and ends with the return character. This means it doesn´t work as an xml file.
Does the write command leave these things or have I done something wrong?
Fuga.
Hi.
I developed a db on a local verison of Access 2003 (saved in Access 2000 format). It works great thanks to much help from this forum.
I recently moved the db to a server and tested how it worked when using Access 2000 on a citrix platform. This is important since the users of the db will be using this accursed platform. A few things (vba functions) broke but I deleted the affected code. However, one thing 'broke' that I can't eliminate.
When I click a button on one form I have a transfer spreadsheet command run to export the output of a query to a particular xls file/location. However, in the citrix environment I get an error message "Formats that enable export as html, xls, or rtf are missing from the windows registry".
I'm guessing this has something to do with the citrix server setup. Is there anything I can do on my end to avoid this problem or, as I fear, am I stuffed?
Cheers,
Craig
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
I'm working with Access 2010 and am trying to use the transferspreadsheet command to output data in a query to an Excel 2010 format file. Here is the line of code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "q_calldetails_tmp", "c: emp estoutput.xlsx"
It works fine and produces the output file but when I try and open it with Excel I get an error saying the format is incorrect. If I change the extension to .xls it opens with no problem but I need it to be an Excel 2010 format with correct extension.
I am having problems with an append from a spreadsheet into a pre-populated table. I am using a macro to do this.
The table is in the middle of a hierarchy of tables. For example:
table 1 - Audit - key: AuditNumber & AuditName
table 2 - Process - key: AuditNumber & AuditName & ProcessRef
table 3 - Risk - key: AuditNumber & AuditName & ProcessRef & RiskRef
table 4 - Controls - key: AuditNumber & AuditName & ProcessRef & RiskRef & ControlRef
table 5 - Control tests - key: AuditNumber & AuditName & ProcessRef & RiskRef & ControlRef & TestRef
The front end of the database has a form for each table. Once you create an Audit in table 1 you can create multiple Processes underneath this one audit. Underneath a process you can create multiple Risks...... hence a hierarchy.
I am trying to append data to table 4 - controls. I have copied the format exactly into excel. I have also prepopulated the Audit, Process and Risk data in the required tables.
The reason I want this functionality is to enable team members to populate control information (oftentimes up to 25) in excel and then upload them easily into the database.
The macro I have created will work when created a new table. but when I point it at the existing table 4 - Controls I get the Key value violation error.
I'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.
In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.
The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?
I am trying to return the MIN in a numeric field (single result) from my database and I have no issues doing this BUT the problem is that I also need to display additional fields in order to make the result useful. The additional field can't be grouped by b/c that changes the result of the MIN function.
View 3 Replies View RelatedI have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.
Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG
Is there a way of merging 2 fields together to create an additional field
my database consists of 4 main tables (in order of relationships)
*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016
For example;
Account Reference: TEST
Site Number: 001
and the field i would like to have;
Site Reference: TEST/001
I would also like that when i add a new site to that account i will have TEST/002....
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued
0001 01/11/2013 Gloves (Orange) 10
0001 01/11/2013 Hard Hats (Black) 2
0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
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"
[Code] ....
I am not sure if it is possible to implement this but I would like your input. We get request from client via a web based form which contains a unique ID number called RDEFNumber. When we process the request internally, we use this number to identify the request throughout the process. The request from client contains processing of multiple files which are transmitted to us in different times of the day or days.
Every time we receive a file, an email is sent to the IT team with all the information about the file. We use an internal database to send the email to the IT team and use the RDEFNumber as the identifier in the subject line. What I am trying to do is to add an additional number at the end of the RDEFNumber automatically to differentiate the subject line.
For example, we take "1791" as a RDEFNumber. For the first time we enter 1791 to the database RDEFNumber field, it should check the table "tblRDEF" (record source) for duplicate value. If no Duplicate exists, it should keep the number as same. When we get the next file for processing with the same 1791 number and enter this number in the RDEFNumber field, it should automatically change it to "1791-1" since "1791" already exists in the database. When we get the next file, the number should change to "1791-2" and then "1791-3" and on.
so i have MainForm and PopUp (which is also a form). on the MainForm there is a command button that brings up the PopUp form. on this PopUp form, some data will be entered then the PopUp form will be closed via a command button.
whats the code i would need to take a value from a control on PopUp and place it in a control on MainForm, ideally when the command button closes the PopUp form.so i know it has to go on the command button's on click event, probably before the close form code lol... but i cant figure out how to get the value to transfer over..
I would like to display a text entered in form1 (text box) to another combo box which is appearing in form2.
View 14 Replies View RelatedI have a simple query and I want to export it in excel, Comma Separated Value., .CSV
I tried to run DoCmd.TransferText but all of my attempts were futile.
I found it, I must export it as TXT and file suffix insert .csv instead of .txt and it works
Now can I execute this "Saved Exports" through a macro or docmd ?
I insert a picture just to make it more clear.
[URL]] ......
I have a textbox with about 400 characters, I also have 5 textboxes next to it. I want to click a button and I want to transfer the characters to the other textboxes 208 characters at a time.
View 3 Replies View Related