And you may want to check for nulls If IsNumeric thisdb. You could then grab the spreadsheet name and import with a specification. You might want to do a If IsNumeric thisdb. You can use the worksheet. I have a database of names in one excel file. Close To this: 'close workbook oExcel. It did take a minute to open though.
Any help would be greatly appreciated cause I am going nuts here. . Is there a code that I can use for other files? Rob Hi Daniel, From the threading I see here, it seems you're replying to my original post; however, your question seems as if it applies to my follow-up reply to chunchencool. RecordCount 'code to skip importing of headings 'thisdb. However, if the data ultimately comes from the back-end, why not have Excel query the back-end directly? What I have come to realize is that if the workbook which is being opened, is already opened by another user, Excel does not indicate that. Any help would be greatly appreciated cause I am going nuts here.
Even if I save the file as a copy, I can only open the new file read only. In other words, you would have versions of these files locked down throught their file properties. MoveNext Loop 'Next i I'm winging this, have no idea if this will work. Thanks for helping make community forums a great place. Rob Hi, I also didn't change the filename when using SaveAs method. If you need a stored query for Excel to call, why not save that in the back-end? AddNew 'starting at field1 since field 0 is an authonumber.
Thanks both for your help. My original - which worked perfectly well - did not have that. MoveNext Loop 'Next i I'm winging this, have no idea if this will work. I've tried changing the code to this: ActiveWorkbook. About error 1004, I think it is because you were trying to save as a filename which had already exists, in this case it is your original file. There is more than one spreadsheet coming from different sources but esentially have the same sort of data in different formats and by importing the data this way I esentially do not have to modify the reports, queries and relationships that are already applied. Worksheets 1 Try something like this: Dim excelApp As Excel.
The connection string is like the registry. Select No, the file definitely exists. I have inserted a bit of code which I got of the internet to display a messagebox but the coding did not work, so I took it out again. If you really want to open the file and not allow edits at all, then you need to protect the workbook. Perhaps you can set it via a routine. Perhaps you can set it via a routine. RecordCount 'code to skip importing of headings 'thisdb.
Rob If you read my original post carefully, you'll see that the changed Open line with the ReadOnly parameter is an attempt to set the ReadOnly parameter to False when I open the file - and that fails. And it did just like you said. There's a refresh button there. MoveFirst 'code to skip importing of headings 'thisdb. It now fails on the. It seems that no-one is able to provide an answer as to how to get Access 2003 to open the file in read-write mode, or even to verify that this is indeed the problem when Excel 2007 is installed. This is a problem because I don't want to have to case out all different file types, and I want to stay away from extra libraries as much as possible.
I think the read only problem was because it turned out that I already had that file opened. I recalled from the old days or Lotus I'd enter a number in a cell as a number but if it were an alpha I'd enter an apostrophe before the number to define it as a label. And you may want to check for nulls If IsNumeric thisdb. Thanks for your reply twinnyfo, having the spreadsheet refresh on open is very convenient, especially as it allows me to provide different individuals with different views of the data as we operate a 'Need to Know'system. Do I need to trap this error and handle it in an error handler? Rob Hi, Since you said you want to change the FileFormat, are you sure the file name is matched to the file format? If it failed on that, you know your code needs to be updated. I have tried saving the file as Excel 2003 and saving it to my desktop, to see if it is the version of Excel, or my folder permissions, but neither works.
I was about to say that I can't use the Save method, because I want to change the FileFormat. Click to participate the survey. I have it automatically refresh the data when opened and automatically save when closed. MoveNext Loop 'Next i Paul wrote: I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. Once a new record is updated, that record and its other related records get updated into an excel file - which can be displayed as read only in a OneDrive weblink. I am using access 2000 and excel 2000. If I sort the coloumn in the excel file and the first character in the cell read is alphanumeric then only alphanumeric values will read and write in to the table.
Why is this situation different? In my Excel Options, I have enabled all macros. I cannot do the same thing programmatically from Access 2003. Hi Luna, I take your point about matching the file extension to the file format. Depending on how you setup the other options. ChangeFileAccess xlReadOnly This code does in fact change the file to read-only. All contents © 1998 - 2019 MrExcel Publishing All rights reserved.