Search results

Morning All I have the following code (created with a lot of help from forum users) which loops through a query, creates an Excel file (with formatting) in a directory on C:\ The query examines a fairly large table to identify missing data in 32 fields of the table for 134 different countries...

That's not all that easy. It's really large. However, when I changed xlApp.Visible = True to false which I wanted to do anyway, it fixed the issue. Many thanks for your help.

Thanks for this. Really works well but one small thing. At the end of the process, it leaves Excel open on screen.

Hi all I am trying to run code that formats an excel spreadsheet after it is created in Access. My code is: Private Sub cmdExportMissingDataFiles_Click() On Error GoTo ErrorHandler Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT CountryCode, Country FROM...

Thanks Pat. I agree now that this is overkill. I have tested and tested the imports and they work perfectly. Perhaps a bit of insecurity around the import process at first but I am now convinced that I don't need to do this.

Hi June7 Thanks for this. I can certainly get intCnt to work for a specific file which is great. I have been trying to place variables in the code for filepath and filename as MyPath and MyFile respectively so that the code can loop through all 134 files and provide the record count for each...

Sorry. It is xmas day here and I won't get to this until tomorrow. Merry Christmas

Thanks for the reply. The problem is that I have 134 spreadsheets from different countries that are appended to one table. I have a loop that achieves this but I was trying to insert code into my import that counts the records before they are imported. Public Function ImportAllExcel() Dim...

Hi All Is there a way to count the records in an Excel spreadsheet from within Access? I want to check the number of records before I import them to Access and then run a check that the correct number of records have been appended to the table. Many thanks

Hi and thank you for your response. I am just looking through the code and I should say that I am an absolute beginner when it comes to VBA. My questions may therefore seem to be fairly simple! How do I change "'DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody" to send...

Hi I have 134 users from different countries who send in Excel files with data for a survey. Some of the files may not be complete and have missing data. To date, I have managed to: 1. Automate the import of all 134 files into a table. 2. Create a query to identify those files that have...

Absolutely perfect! Thanks again. I am relatively new to VBA so am learning on the way. Help like this really is so useful and I just keep learning. Your time and effort is greatly appreciated. Cheers

Now I get "item not found in this collection at line DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & "Missing Data For " & rs!Country & CreationMoment, True

It seems to be working really well now. One last thing if I may impose again. It is creating excel files for countries that have no missing data. If we can fix that I think we are there and I will stop bugging you!

Ok. I copied your sql statement to the query so that should be fine. It should show results CountryCode = xx and any field has missing data. This could be more than one field.

Sorry, I may have jumped the gun with this! I copied your sql to "qryMissingData" and it now only returns data for the first 2 countries. Also, all the country outputted files contain the same data, i.e. Australia and Argentina where I need the file for say, England to have data for that...

Fantastic! I just needed to change the whereclause to match the variable declaration and it worked perfectly. Thank you so much for your help on this, I rteally do appreciate it.

Hi again This is the sql for "qryMissingData" SELECT tblConsolRawData.ID, tblConsolRawData.Country, tblConsolRawData.CountryCode, tblConsolRawData.Data, tblConsolRawData.Fruit, tblConsolRawData.FirstName, tblConsolRawData.LastName, tblConsolRawData.Region, tblConsolRawData.WorkTimes...

Hi Sorry. Have to do some work on the file. It is too large. It may be a few hours as I have to go to work!

Hi Thanks for your response. I appreciate it. As I stated I have never done this before! Your code produced an error message as follows: I am not too sure what that means. I have been thinking about this a bit more and I don't think what I asked help for is what I actually need. To...