HibHib
Last Activity: Jun 23 '17, 08:52 PM
Joined: Jun 19 '17
Location:
-
Right after i tried to find solutions to the problem, looked it up before coming to you for help and saw that that may have been the issue, didn't seem to help however -
Well ill be darned, that worked, even though i didnt think it was fundamentally different from Right$(tdf.Name , 5) = "Input" evidently it is. Thank youLeave a comment:
-
I had already added the delete function in and that still didnt solve the issue, and when i tried adding the filter ofi got and error onCode:Right$(tdf.name, 5) = "Input"
Object variable or With block variable not set. I had the table names printed and they displayed as "~TMPCLP### ##" which i believe is a cached tbl name??Code:rst.Close
Edit: got the thing to close properly, but because of my filter, the fact that the names of...Leave a comment:
-
Does Dao Recordet save old records? Because when i incorporated ADezii's ideas, the code ran but ended up putting 4 times as many records as i currently have, including ones from old tables that i had deleted long ago, severely messing up the table im appending to.Leave a comment:
-
@ADezii
Wow, Thank you for taking so much of your time to create this, it is certainly withing the ballpark of what i am trying to accomplish, i will certainly make use of thisLeave a comment:
-
Ill post the code tomorrow as it is on my work computer, which they dont let me take with me.
"On that point, the total lines wouldn't be considered Normalised. Are you sure they are actually necessary for you?" May you clarify what you mean by this?
Also, the [Employee] field on the table im appending to will be populated by [First] + [Last] from the Worksheets
On the topic of normalization, It would be really nice if...Leave a comment:
-
They are, or in future cases, will be already imported and the names of the tables are based on the worksheet names, which should always be MAY INPUT, JUN INPUT, JUL INPUT, etc. The number of worksheets can change depending on when the excel file is uploaded because the company deletes the months that have passed. The format of every worksheet will be the same but may have a few more columns depending on what projects exist at the time of the upload,...Leave a comment:
-
There is a bit of a misunderstandin g, though i will incorporate a some of what you did because its cleaner code
What my code does is turn this
...Code:Last First Utilization Project A Project B Project C Project D Project E Project F Project G Project H Employee 1 Employee 1 95% 0.2 0.25 0.1 0.3 0.1 0 0 0 Employee 2 Employee 2 73% 0 0 0 0 0.36 0.17 0.2 0 Employee 3 Employee 3 80% 0 0.1 0.5 0 0 0.1 0 0.1 Employee 4 Employee
Leave a comment:
-
Yes, Same number of records in each tableLeave a comment:
-
Yes, but the format of the first tables different
The one table im appending all of the tables to is in that format, the reason im going this seemingly more difficult way rather than import them all into 1 table in the first place is after this im going to be adding more code that creates a month field that distinguishes which month...Code:Field(1)-[First] Field(2)-[Last] Field(3)-[Project_A] Field(4) - [Project_B] `etc
Leave a comment:
-
I am making graphs based off a 12 worksheet excel file, the issue is that the excel files format isnt, to my knowledge, in a way i can make the variable graphs, so im taking the tables that i imported from an import code, and reformatting them so that i can make the graphs correctly in my report.The criteria for the graphs is the project and resourced time for each employee so im taking the field names (starts at 3 because the first 2 are names)...Leave a comment:
-
I may be making this way more complicated than it should be, but basically i want to be able to reformat multiple tables and export them into 1 table, my code atm looks like this, but i get the error of "Cannot Find the input 'tableName'.
...Code:Private Sub Command1_Click() Dim db As DAO.Database Dim rst As New ADODB.Recordset Set db = CurrentDb Dim tbl As TableDef Dim Reset As StringLeave a comment:
-
Seems a lot closer to what im wanting, and more flexible, however it returns the same error of "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." which seems odd do to it working if there's a standard table name there, thank you thoughLeave a comment:
-
Is it possible to use ADODB.Recordset.Open in a loop
I am reformatting an excel sheet to be more access friendly and had to use a recordset, however i have multiple tables that im trying to reformat into 1 new table using vba. When using the function recordset.open the first parameter is Source, and is where i put a table name. To get this to work i tried to establish a loop where it would go through tab1, tab2, tab(n) but i kept getting errors. Is it possible to loop through table names in this function?... -
I actually had that in my code, but with with wsLoop.Rows(1) instead of range because i only needed my fields to have no spaces, my issue was actually a dumb mistake on my part, earlier when i had created a collection, it set the excel sheet to read only, which stopped the replace function from working(Tho oddly not the delete function). Now that that dumb mistake is fixed, the code works! Thank you a ton for the helpLeave a comment:
-
I got it to work right! But then i tried to replace " ", which seemed rather straight forward. but no matter what i tried it kept popping up with an error in excel saying "Microsoft Office Excel cannot find any data to replace". Initially i thought it was because it was checking the deleted rows, so i changed it so search row 3 but the same error popped up.
Also, thank you for helping out a newby like me, i know it can get...Leave a comment:
-
Thank you for the example. I *tried* to take your example slightly further but have run into a slight issue. The excel file im using for this project has 12 worksheets/tabs in the same file(all share the exact same layout, it just different values for different months)i tried to set up a loop that would go through each worksheet to delete the first 2 rows in all of them instead of just one. I established a collection of Sheetnames colWorksheets(l ngCount)...Leave a comment:
-
Thank you for the response and for leading me towards what i need to do. Luckily i'm a full time intern with this as my only current project so i have nothing but time to learn. Also luckily, this SHOULD be the last thing i have to do for the project. I will likely be asking for some help as i move further into this step of the project.Leave a comment:
-
How do you edit an excel sheet before importing it into access
The company i am interning for is having me format data from an excel sheet into a filterable access graph. Im rather new to VBA and at the moment i have a button that imports an excel sheet and re-formats the information to be more fitting to what access likes. I run into an issue however with some of the things that are on the excel sheet side, being that the first 2 rows in all of the sheets are visual fluff and the third row contains what i need...
No activity results to display
Leave a comment: