Recent content by rlarkin

That sounds like a better solution. Thanks for your help jleach, much appreciated.

Hi jleach, I had a dynamic query originally when building the custom datasheet, but I found when you remove them from SELECT you cannot then unhide those columns via the datasheet's unhide option on right-click. I liked the extra functionality it gave the user to be able to add in an column...

SOLVED: Saving hidden/unhidden columns in datasheet Hello, I'm struggling with this one. I have a form, which contains a subform, which is a custom datasheet. It's custom in that the user selects the columns they wish to see (achieved by using ColumnHidden), along with any criteria they've...

Would anyone be able to advise how to access the pop-up dialog you get when you click to unhide/hide columns in a datasheet? I'm looking to have it pop-up on command. Thanks, Russ

Great! Easy when you know how. Thanks everyone.

Sorry, yeah should have mentioned, it's a local file. The file stays open while a form is open, which could be for some time. Just in case there is a problem that means Access doesn't close properly, and the Excel workbook is stuck open in the background, I'm looking for a way to close the...

Hello, I'm using VBA in Access to open Excel workbooks with the following code: If Not FileLocked(strFileName) Then Set objApp = New Excel.Application With objApp Set objBook = .Workbooks.Open(strFileName, , False) End With End If The If statement checks if...

Thanks, Bob. The spreadsheet is opened to update it, but this isn't always inclusive to using the Access part, which currently only builds reports from the information. As such, the values will only change if it's been upated, and therefore opened. I guess what I was looking for was a way to...

If I just run the import and accept the errors, it does it in a fraction of the time, so I was hoping I could find a way to fix the errors, without resorting to opening the Excel spreadsheet beforehand. I must admit I'm confused by your remark, and must be missing something, as opening the...

Hello Bob, That's something I considered (I was actually forced to do that when the spreadsheet was linked), but I didn't want to cheap out and just open the spreadsheet if there was an actual way to fix the issue. I'm accessing the import sheet over a network, so it's not practical. I'll...

It's a shame, as it works absolutely fine when open. Is there any chance there is an Excel formula code that will give a definite Null value? I could just delete the import errors, but it's bugging me.

Hi, I'm importing an Excel spreadsheet, which has formulas that output either a value or a blank ("") into the cell. I've had problems getting Access to accept the blank cells with numbered and dated columns. I managed to get this to, sort of, work by using NA() instead of "" in the Excel...

objBook.SaveAs strSheetFolder & strSheetFile,,,,, False Okay, I got SaveAs to work, I was just being stupid. Apparently DoCmd.TransferSpreadsheet turns on "Always Create Backup", so if you just .Save it'll create a backup.

Hello, I'm using code to reopen an excel worksheet from Access and apply formatting, but I'm having issues with getting it to save without creating a backup. Here is my code: If .chkAimCert.Value = True Then DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _...

I'm building the SELECT part, to decide what columns appear in the table/report/exported spreadsheet, all the rest will stay the same and is tacked on at the very end. So, at the moment, it's basically: Loop selected items. Add string to strSelect if selected. strSQL = "SELECT" & strSelect &...