Recent content by greebo

Thanks for your quick response The actual code is: Dim appExcel As Object Set appExcel = CreateObject("Excel.Application") appExcel.Workbooks.Open "d:\imports\tesfile.xls, UpdateLinks:=0 stepping over the 3rd line gives: "This file is not in a recognizable format", etc, etc with [OK] and...

The Problem: I have an unattended database (triggered by scheduler) which imports excel files into access after checking the contents and format, but the process halts at "appExcel.Workbooks.Open" if it is unable to open the file - such as a zip file changed from .zip to .xls (I'm not...

Thanks for the compliment! I will definitely use the 'KillProcess' and cleanup code Thanks for your help Greebo

Thanks to all for your help! I was getting the error message at [Selection.NumberFormat = "General"] and I couldn't work out a way around it. based on Smeghead's solution, I change the code to [Wks.Columns("M:N").NumberFormat = "0"] and now it works quite happily. The problem started with...

I have an application which imports excel files submitted from wordwide sources. although the format is supposed to be fixed, people play with them so you can never be sure... I always import each file into a new table (standard name) then run an access query to delete the unused lines, move...

sorry rickysymo, but after 25 years in computing, I have decided to retire rather than learn Office 2007. Thanks Dcracke, I have changed the end of the module to: appExcel.ActiveWorkbook.Close SaveChanges:=True appExcel.Quit Set appExcel = Nothing But I am still getting problems Should I...

I have an app which validates and imports excel files into Access with a module that checks the Tab names then sets certain columns to "General" format. The first file works ok then the second call of the module gives Run-time error 91 - "Object variable or With block variable not set" Module...

Working perfectly, Thanks for your help. I generally get there eventually with Access VB but when it gets mixed with Excel, I am so far outside my comfort zone! Cheers, greebo

I have created a function for checking the Tab names in Excel workbooks before importing them (despite instructions, users will change the tab names before sending their monthly returns!) ***************************************** Function fncXLTabCheck(strPath As String, strImpFile As String)...

Andy, Re compacting databases, is your application split into a front-end and separate back-end data store? If yes, do you use access or SQL for the long term storage? My clients have their front-end apps refreshed at every network logon (via a batch file). The master copies are saved in a...

Now working - Thanks to everyone for your help! Editolis - my solution is based on your code. I'm very grateful as I can add the column and not have to reject the entire submission. Dave - your code will enable me to validate and reject where critical columns have been deleted. Ken - Is it...

Ken, Unfortunately I am not allowed to lock down the template. Some users manually enter 50 - 100 lines per month but most use feeder systems to download anything from 5,000 to 20,000 lines and paste these onto the sheet. The manual process has worked effectively for over 2 years, but needed...

Ken & Dave, In the past, the report data was replicated on an import sheet and each column was prefixed with an alpha character to ensure that all data imported as text - avoiding the usual problem of mixed alpha/numeric data in fields. This also ensured that I had a least an alpha character...

I really appreciate both your help! Unfortunately the length test gives me "Item not found in this collection" which halts the code. Using the application objects, can I set field AE3 to "Comments" then save and close the file? Sorry to be a pain, but my knowledge of Excel VBA is very basic...

Unfortunately some users complete the column(Title:Comments) and expect the information to be recorded and distributed. There are just a few users who decide to delete the column - despite detailed instructions on how to use the spreadsheets. The spreadsheets cover upto 100,000 transactions...