Search results

Thanks for the reply. The sheet will always be the second sheet of the workbook. Not sure why the logic of the changing sheet names makes a difference, but its from another departments automatic reporting system which names the sheet as a date and time stamp, which is frustrating when you're...

Hello, Please can someone tell me how to import an Excel worksheet in VBA when the name is unknown? I can use DoCmd to import using the sheet name, but because the sheet name is ever changing I need something more dynamic. Thanks

Thanks for the advice. I went a different way in the end. I have a function in a module that gets the user name. In the combobox default I just added =MyFunction() which picked out what I wanted.

Yes - it highlighhts xlBook.Worksheets("Data").Range("A2").CopyFromRecordset (Rs)

Fantastic - Thanks!

Hello, Im a bit confused with a debug im getting. The code im running is very simple and works in my 2003 database (Im transferring to 2007). Below is the code. The Path and FileName are both being read correctly. FOr some reason its giving a compile error variable not defined. Its...

Sorry - gave you the wrong error code there. I put that line in the wrong place. This is what I currently have which gives me runtime 430 class does not support automation or doesn not support expected interface. Private Sub REF_cmd1_Click() Set DB = CurrentDb Agent =...

Hi namliam, Ive tried the xlBook.Worksheets("Data").Range("A2").CopyFromRecordset (RS) but it debugs with runtime 91 object variable or with block not set. This is my test code and at the moment all I want it to do is open and close without leaving Excel running as a process. Once thats...

Ive tried what you just said and still debugs on the same line.

The gap in the Recordset isnt in the code. For some reason it appeared after i submitted that post. With xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset(RS)

Sorry - that would have been helpful wouldnt it... Its line: With xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset(RS)

Thanks - doing that gives me Runtime 13 - Type Mismatch Private Sub REF_cmd1_Click() Set DB = CurrentDb Agent = [Forms]![x_test]![PMA_lst1] Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open("C:\Templates\01. PMA Report (RMO).xlsm") Set xlSheet...

The idea was that the data transferes to the xlsm, the xlsm macros run, which creates and saves a named xlsx workbook. The reason for this is that the data makes the file size quite big (for email). The xlsm macro refreshes pivots, changes them from pivots to normal tables, deletes the source...

Thanks - I still cant seem to Kill Excel though. It stays running as a process and then when I export again it debugs.

Thanks. I still have problems though and I think Ive tracked down what it is. Excel is closing but the process remains. Ive looked through loads of threads about this and I 'think' it may be because of non explicit references? Ive tried the below code but clearly im not getting it right. Any...

Hello, I'm having a problem with Run Time 1004 – Method worksheet of object global failed. My VB opens an Excel template and copies data too it. It works every other time, debugging at ‘Set xlSheet = Worksheets("TestSheet")’ on the times it fails. Can anyone see aht could be going wrong...

Hi All, I have a function that creates and email in Outlook but I want to add a ‘Top 10’ list to the email body. The data will come from a query. This is my ‘create email’ function but I have no idea how to add the query result to the body. If any one can help that’d be great. Thanks...

Hello, Can anyone tell me if its possible to change a listbox multiselect property in VB? Ive tried a few things like lstbox.multiselect =true/Simple/1, but its not right. Thanks

Hi and thanks for replying. The mdb table to be imported is created by a program and part of the table name is the date (YYYYMMDD), hence the name of the table being different each day. At the moment useres are manually importing the mdb table, then renaming it. I want to automate this so that...

Hi everyone, I've come across a problem with importing access tables from one mdb to another in VB. Usually I use FDialog and TransferDatabase. The item im working on right now needs to import a table from 1 mdb (MDB1) to another (MDB2), but the table name in MDB1 changes daily. Can anyone...