opening specific excel spreadsheet from access Results


Page 1 of 2.
Results 1...20 of 33

Sponsored Links:



Hi,

I have no real knowledge of VB. I was hoping to open a specific excel file from an access form. Access 2007 doesn't have the RunApp macro like previous versions did, and i was wondering how to write some VB to open the file i need

I would pull the file into access, but for this project it is easier to run the spreadsheet seperately.

The file path is "T:IV NVQ FilesAssessor Monitoring Spreadsheet"


Many thanks for your help




Hi all,

I am trying to learn how to read from and write to specific cells in an Excel spreadsheet, from a VBA module in Access. This is my first try at this and I am currently playing around with some code and ideas.

All it does so far is run a sub to tell me what's in a particular cell in the spreadsheet, and then run another sub to write to a particular cell and then tell me what's in that too. The second sub references the cell at an intersection of two named ranges (probably not relevant).

Everything works as I expect it should, except that when I then open the actual spreadsheet, none of the worksheets are visible. I only know the file has actually opened because when I go to close down Excel I get the standard "Do you want to save changes to..." message. Other than not being able to see it, the file seems to be in tact.

Here is my code:


	Code:
	Option Compare Database

Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook

Dim strDBPathAndFile As String

Sub main() 'main sub

    Call connectToSpreadsheet
    Call readSomeCells
    Call writeSomeCells
    Call disconnectFromSpreadsheet
    
End Sub 'end main()

Sub connectToSpreadsheet() 'Connect to the spreadsheet.

    strDBPathAndFile = CurrentProject.Path
    Set xl = CreateObject("Excel.Application")
    Set xlWrkBk = GetObject(strDBPathAndFile & "/TstSpreadsht.xlsm")
       
    Set xlsht = xlWrkBk.Worksheets("Input_Variables")
    
End Sub 'End connectToSpreadsheet()

Sub readSomeCells()
    Dim a As Integer
    a = xlsht.Range("C7")
    MsgBox "It's " & a
End Sub 'end readSomeCells

Sub writeSomeCells()
    Dim nm As String
    nm = "Inp_WE_30_07_2010 ResILT"
    xlsht.Range(nm).Value = 805
    Dim b As Integer
    b = xlsht.Range(nm)
    MsgBox "It's " & b
'    xlWrkBk.Save
End Sub 'end writeSomeCells()

Sub disconnectFromSpreadsheet() 
'    xlWrkBk.Close
    Set xl = Nothing
    Set xlWrkBk = Nothing
    Set xlsht = Nothing
    
End Sub 'enddisconnectFromSpreadsheet()

I suspect my problem may be with the 'disconnectFromSpreadsheet' sub, as this may not be disconnecting at all. I'm having trouble conceptualizing connecting / disconnecting.

Can anyone offer some advice on how best to perform these tasks along the lines I'm heading, but without the 'disappearing' spreadsheet?




Hello,

I am trying to figure out how to click on a button in an Access Form to create an Excel spreadsheet with certain rows and columns hidden. This specific example is supposed to create an Excel workbook with a single sheet named "TheOnlyWorksheet" containing only the autofited "A1" Cell containing the words, I am the only one". This Excel file should remain open until closed by the user within Excel (Access should remain open at all times).

I can push the button in the access form and get the resulting spreadsheet displayed as I thought it would. However, once closing Excel (from within excel) and pushing the button in the access form again, the spreadsheet does not appear - instead I get the error: "Run-time error '91'; Object variable or With block variable not set" and it stops on the line ".Range("B:B", Selection.End(xlToRight)).Select"

I am not sure what that means or how to fix it.

I also went into Task Manager after the first run of the code and having closed Excel, but EXCEL.EXE is still listed in the Process list - why did it not close when I closed the excel program?

To make it more strange, after the first run that works correctly, if I go into Task Manager and end the EXCEL.EXE Process, I then get an error "Run-time error '462'; The remote server machine does not exist or is unavailable". Pressing the Debug button shows me that it stops the code on the same line again, ".Range("B:B", Selection.End(xlToRight)).Select"


Below is the code for the button on the access form?

	Code:
	Private Sub cmdHide_Click()

''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Must Reference Microsoft Excel 11.0 Object Library '
'               Tools>References...                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'   Declare variables
    Dim objXLApp As New Excel.Application
    Dim objXLWorkbook As Excel.Workbook
    Dim objXLWorksheet As Excel.Worksheet
        
'   Create an instance of Excel workbook with one sheet in Excel
    
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLWorkbook = objXLApp.Workbooks.Add
    Set objXLWorksheet = objXLApp.Sheets(1)

'   Specify how many worksheets there will be in the new workbook
    objXLApp.SheetsInNewWorkbook = 1

'   Name single sheet
    With objXLWorksheet
        .Select
        .Name = "TheOnlyWorksheet"
    End With

'--------------------------------------------------------------------
'   Hide Cell Ranges
    'Must do after making aplication visible or else it does not work
    'Hide Columns
    With objXLWorksheet
        .Columns("B:B").Select
        .Range("B:B", Selection.End(xlToRight)).Select
    End With
    Selection.EntireColumn.Hidden = True
    

    'Hide Rows
    With objXLWorksheet
        .Rows("2:2").Select
        .Range(Selection, Selection.End(xlDown)).Select
    End With
    Selection.EntireRow.Hidden = True
'---------------------------------------------------------------------

'   Enter Data into cells and fit cells to text
    Range("A1").Value = "I am the only one"
    Columns("A:A").EntireColumn.AutoFit

'   Make Excel Application visible
    objXLApp.Visible = True

'   close objects
    Set objXLWorksheet = Nothing
    Set objXLWorkbook = Nothing
    Set objXLApp = Nothing
    
End Sub

Any Help would be greatly appreciated - Thanks in advance.




hello,
I would like to open an excel spreadsheet, a macro will start and it will import specific table columns from multiple mdb files (from about 30 files). The kicker is when the tables in the mdb files are created, they are assigned random names, but the column names are always the same.

Has anyone done that before?

Thank you




Hi sorry to post again, but you are all very helpful and ive almost finished by DB.

I have a button that has to export 4 queries into excel spreadsheets, and then open an excel spreadsheet that has links to the 4 exports and does some nuber crunching and produces a report.

the button code is:

Dim fullpath As String
fullpath = CurrentProject.Path
' exports expenditure
DoCmd.OutputTo acQuery, "qryExpenditureExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "Exp.xls", True, "", 0
' exports income
DoCmd.OutputTo acQuery, "qryIncomeExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "In.xls", True, "", 0
' exports priority debts
DoCmd.OutputTo acQuery, "qryPriorityDebtsExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "PRIO.xls", True, "", 0
' exports non priority debts
DoCmd.OutputTo acQuery, "qryNPDExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "NPD.xls", True, "", 0
' Access form button
Call OpenSpecific_xlFile


It calls this code to open the file:

Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sfullpath As String
Dim sPath As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sfullpath = CurrentProject.Path & "FinancialStatementTemplate.xlt"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sfullpath)
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
' Run it from a form button

Private Sub cmdOpenExcelFIle_Click()
' Access form button
Call OpenSpecific_xlFile



I need to then make sure that FinancialStatementTemplate is then the top window so that it can be printed.

how do i set the focus on this specific window?

Thanks in advance.

Kev




Hi. My name is Ron Pleasant and Iím having a problem with VBA that Iím hoping the community can help me with.

I am trying to copy a recordset to a specific range of a specific Excel worksheet in a specific Excel workbook.

This programming worked perfectly when I was using Access 2007; when I transitioned to Access 2010, I started getting a Ď430í error Ė ďclass does not support Automation or does not support expected interfaceĒ. Below is the coding:


	Code:
	    dwnldThis = " SELECT [06_SP485 RPTG TEMPLATE].* "
    dwnldThis = dwnldThis & " FROM [06_SP485 RPTG TEMPLATE];"

    Set myApp = CreateObject("Excel.Application")
    Set myFile = myApp.Workbooks.Open(filePathName, , True)
    Set targetTab = myFile.Worksheets(sheetname)
    Set targetRange = targetTab.Range("A2")

    myApp.DisplayAlerts = False
    myApp.Visible = True
    targetTab.Visible = True

    Set myRecset = CurrentDb.OpenRecordset(dwnldThis)

    targetRange.CopyFromRecordset myRecset

Here are the libraries Iím referencing:

1. Visual Basic For Applications
2. OLE Automation
3. Microsoft Office 14.0 Access database engine Object Library
4. Microsoft ActiveX Data Objects 2.8 Library
5. Microsoft Access 14.0 Object Library
6. Microsoft Excel 14.0 Object Library
7. Microsoft ActiveX Data Objects recordset 2.8 Library

Iím new to Access and programming in general; I have no clue of what all is in these libraries but based on everything Iíve read (and the fact that the programming worked in the 2007 version), I have the appropriate libraries referenced (for what Iím trying to do).

Can anybody help me with this? Thanks much in advance. Ron




Hi,

I'm trying to import specific cells from MS Excel 2000 spreadsheets to MS Access 2000 tables then move the spreadsheets to a different directory.
I'm very new to this and I'm having trouble to implement this.

I have worked out so far the code to import certain cells into 1 table, but I do not know how to import some other cells into another tables so the data would be connected and remain together.

So lets say that I have 2 tables named tblXls and tblXls2 in and Access 2000 Database and I want to import cells F2, C2 and J2 from excel spreadsheets into the fields Test, Test2 and Test3 in the table tblXls, and then I want to import cell F3, C3 and J3 into the fields Name, Phone, and Address in the tblXls2 table. How do I do this?

Here is the code I have so far for the 1 table:



	Code:
	Private Sub xlsAdd_Click()
 
Dim rec As DAO.Recordset
Dim xls As Object
Dim xlsSht As Object
Dim xlsSht2 As Object
Dim xlsWrkBk As Object
Dim xlsPath As String
Dim xlsPath2 As String
Dim xlsFile As String
Dim fullXlsFile As String
Dim fullFile As String
Dim fullFile2 As String
 
Dim Msg, Style, title, Response
  Msg = "Importing is Done, Files are imported!"    ' Define message.
  Style = vbOKOnly
  title = "Import Mesage"
 
    xlsPath = "C:Xls"    ' Set the xls path for new files.
    xlsPath2 = "C:Xlsdone"    ' Set the 2nd xls path to store imported files.
    xlsFile = Dir(xlsPath & "*.xls", vbNormal)     ' Retrieve the first entry.
    
    Do While xlsFile  ""    ' Start the loop.
        ' Ignore the current directory and the encompassing directory.
        fullXlsFile = xlsPath & xlsFile
        fullFile = xlsPath & xlsFile
        fullFile2 = xlsPath2 & xlsFile
        If Right(fullXlsFile, 4) = ".xls" Then 'import it
        DoCmd.SetWarnings False
        Set xls = CreateObject("Excel.Application")
        Set xlsWrkBk = GetObject(fullXlsFile)
        Set xlsSht = xlsWrkBk.Worksheets(1)
        Set xlsSht2 = xlsWrkBk.Worksheets(2)
                
        'Open 1st table
        Set rec = CurrentDb.OpenRecordset("tblXls")
        rec.AddNew
        rec.Fields("Test") = Nz(StrConv(xlsSht.cells(2, "F"), vbProperCase), "bad1")
        rec.Fields("Test2") = Nz(StrConv(xlsSht.cells(2, "C"), vbProperCase), "bad2")
        rec.Fields("Test3") = Nz(StrConv(xlsSht.cells(2, "J"), vbProperCase), "0001110000")
        rec.Fields("Test4") = Left(xlsFile, 10)
        rec.Update
        
        'How do I open the second table here to continue exportind the rest of the data?
        
        DoCmd.SetWarnings True
        End If
        
        'Closing excel
        xlsWrkBk.Application.Quit
    
    'Moving the imported Excel file
    Name fullFile As fullFile2
    xlsFile = Dir()
        
    Loop
Response = MsgBox(Msg, Style, title)
End Sub

Please Help. Thanks.




Ill do my best to describe what I need to do...

Please keep in mind, I am a noob and the only macros i know how to run are very basic.

I have MS Access run some compares, then I output a few excel spreadsheets.

I then open these EXCEL spreadsheets and manually input all these fingures into the Rumba/AS-400 Mainframe Session.

Is there a way to run a macro that will take specific cell information from excel, or have access directly input, into RUMBA??

I can run macros in Excel, I can run macros in Access, I can run macros in RUMBA...... I just cant get one prog access another???




I have created a form, a menu if you like. I have a command button set to open a specific excel spreadsheet that was origianlly created in excel, so it uses formulas such as "weeknum".
When i click on the button to open this on my access form, i get #name? instead of the weeknum. Is there anyway i can get this to show the weeknum as entire sheet uses this for forecasting. i have tried using format(now(),"ww") in the spreadsheet itself to replace weeknum(), hoping that when loaded from access it would work, but no luck,

thanks in advance

paul




Hi Guys,

Can anyone advise if this is possible?

We have a standard order form/subform with customer and delivery details in the main form and specific order details in the subform.

I want the developer to include a command button that opens an EXCEL spreadsheet template and auto enters data from feilds in the form/subform into specific cells in Excel. For example the order number text field in the database main from would go to cell A12 in excel, the order item number field from the subform would go to cell D46 in excel.

The operator would save the excel file to there desktop if they want, or print and then on the close from excel they would automatically go back to the access form.

I thought this was totally possible in Access2010 and not to complicated at all to setup but have been told its very complicated and would need "tons of code".

Is this possible, It was an important part of my form design idea.

If its not that complicated is there anything I can pass onto the developer to point him in the right direction?

Note: Please don't say "why not just design a report in Access", we need it sent to excel to work in with an invoicing package we run.

Thanks

JohnG




I have some code that imports an excel spreadsheet into access and creates a table. I recently received a new spreadsheet that has many sheets. I only need the data from one specific sheet, resources. How can I make adjustments to the code below so that only the data on the resources sheet is imported into a new table?
Code: Option Explicit Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _ "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long Private Type OPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long lpstrFilter As String lpstrCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long lpstrFile As String nMaxFile As Long lpstrFileTitle As String nMaxFileTitle As Long lpstrInitialDir As String lpstrTitle As String flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As String End Type Public Function CreateProjects() Dim OpenFile As OPENFILENAME Dim lReturn As Long Dim sFilter As String Dim WrksheetName As String Dim i As Integer Dim oApp As Object OpenFile.lStructSize = Len(OpenFile) 'OpenFile.hwndOwner = Form.Hwnd 'OpenFile.hInstance = App.hInstance 'sFilter = "acSpreadsheetTypeExcel (*.xlxs)" & Chr(0) & "*.xlxs" & Chr(0) OpenFile.lpstrFilter = sFilter OpenFile.nFilterIndex = 1 OpenFile.lpstrFile = String(257, 0) OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1 OpenFile.lpstrFileTitle = OpenFile.lpstrFile OpenFile.nMaxFileTitle = OpenFile.nMaxFile OpenFile.lpstrInitialDir = "E:" OpenFile.lpstrTitle = "Choose a File" OpenFile.flags = 0 lReturn = GetOpenFileName(OpenFile) Set oApp = CreateObject("Excel.Application") oApp.Visible = True oApp.Workbooks.Open OpenFile.lpstrFile oApp.Range("A1").Select 'Finds and replaces spaces with an underscore oApp.ActiveCell.Value = Replace(ActiveCell.Value, " ", "_") oApp.ActiveCell.Offset(0, 1).Select Loop Until IsEmpty(ActiveCell) With oApp .Visible = False DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, "Temp", OpenFile.lpstrFile, True End With oApp.DisplayAlerts = False oApp.Workbooks.Close oApp.DisplayAlerts = True oApp.Quit Set oApp = Nothing End Function Thanks for your help!




Howdy

I've got this module which is suppose to look all the tables listed within a table & find the customers within these tables and get the averages for each question of the customer surveys. However for some reason the sql statement is trying to find every customer in every table and if it cant find it, it flags an error. Heres the Code:


	Code:
	 
Dim db As DAO.Database
Dim varItem As Variant
Dim strSQL As String
Dim rs As Recordset
Dim I As Integer



Set db = CurrentDb()

strSQL = "SELECT Customer.Customer FROM Customer WHERE Customer.CustomerView='y'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
    Do While Not rs.EOF
    
    strCustomer = rs!Customer
  
    strSQL = "SELECT budata.bu, budata.BUnumberOfQuestions2, budata.BUpreSLAQuestions FROM budata"
    
    Set rs2 = db.OpenRecordset(strSQL)
    If Not rs2.EOF Then
        Do While Not rs2.EOF
        
        strBu = rs2!bu
        
        

    strSQL = "SELECT Round(avg([Score1]), 1) As Score1Avg, Round(avg([Score2]), 1) As Score2Avg, Round(avg([Score3]), 1) As
Score3Avg, Round(avg([Score4]), 1) As Score4Avg, Round(avg([Score6]), 1) As Score6Avg, Round(avg([Score7]), 1) As Score7Avg
FROM [" & strBu & "] WHERE [" & strBu & "].[Customer Business Unit / Group] = '" & strCustomer & "'"
        'MsgBox strSQL

Set rs3 = db.OpenRecordset(strSQL)
        If Not rs3.EOF Then
        Do While Not rs3.EOF
        
    strScore1 = rs3!Score1Avg
    strScore2 = rs3!Score2Avg
    strScore3 = rs3!Score3Avg
    strScore4 = rs3!Score4Avg
    strScore6 = rs3!Score6Avg
    strScore7 = rs3!Score7Avg

Set rs4 = db.OpenRecordset(strSQL)
        If Not rs4.EOF Then
        Do While Not rs4.EOF

        strSQL = "UPDATE [" & strCustomer & "] SET [Score1] = " & strScore1 & ", [Score2] = " & strScore2 & ", [Score3] = " &
strScore3 & ", [Score4] = " & strScore4 & ", [Score6] = " & strScore6 & ", [Score7] = " & strScore7 & " WHERE (([" &
strCustomer & "].BU)='" & strBu & "'" 
        MsgBox strSQL
        CurrentDb.Execute (strSQL)


        
        rs4.MoveNext
        Loop
        
        rs4.Close
        Set rs4 = Nothing
        Else
        MsgBox "Shiiit"
        End If
        
        rs3.MoveNext
        Loop
        
        rs3.Close
        Set rs3 = Nothing
    Else
        'no issue
    End If
    
    rs2.MoveNext
    Loop

Else
    MsgBox "Something is wrong2"
End If
rs2.Close
Set rs2 = Nothing
    
    
    rs.MoveNext
    Loop

Else
    MsgBox "Something is wrong"
End If
rs.Close
Set rs = Nothing

MsgBox "Data Analysis & Compile Complete, Please Open the Excel Spreadsheet and Refresh the Query"

Set db = Nothing
DoCmd.Close
   
End Sub

Access is flagging the Update statement saying it cant find a specific customer in a table, however this obviously means my initial select statement just isnt working!

If someone could take a look it would be much appreciated!

Cheers




Hi all. This will be my first post on these forums.
I've searched and browsed through a number of really detailed support threads, most of which were far over my head, and I haven't been able to find a solution that I can apply to the specific issue I am facing.

Here is the situation I am in. If anybody can help, it will be most appreciated.

I am working with an external client who currently uses Microsoft Excel (2000) to manage a series of spreadsheets for their own customer accounts. They have a few hundred of these documents where they track the specific items a customer has purchased, and the most current prices of these items. The spreadsheets are printed and sent out to the customer on a monthly basis so they have a record of the most recent prices for each item.

Currently, this client spends many hours at the end of each month manually going through all these hundreds of Excel spreadsheets and updating the items with the increased pricing. There is a lot of redundant clerical work involved, and that's where I come in.

I have been asked to provide an automated solution to help cut down on the amount of work needed every month to update these existing spreadsheets. The criteria for updating the spreadsheets is pretty simple, and I have the logic of the updates all planned out in my head. However, the actual implementation of this is beyond me.

Here's what I need to do:

1) Take two sources of input data; one is the spreadsheet to be updated, the second is another spreadsheet with a master list of all the items and their price increases for the current month.
2) If an item on the first spreadsheet exists on the second sheet, then it has a price increase and the first spreadsheet needs to be updated based on the increase listed in the second sheet.
3) After repeating this for all the items listed on the first sheet, the sheet should be saved (either as the same file or a new file derived from the original file name) and its formatting preserved.
4) This should be repeated in an automated fashion for all the customer spreadsheets.

My first thought was to program something in C++, but that was beyond my experience.
My second thought was that Microsoft Access might be able to perform this work for me, and I am pretty sure that it can. Here is how I am envisioning the process:

1) The original spreadsheets are imported to temporary transactional tables in the Access database.
2) The necessary comparisons and updates are made within Access.
3) An updated Excel spreadsheet is exported from Access with either the same file name as the original sheet, or a new file name derived from it.
4) Automated rinse and repeat through all the source sheets.

The three problems I'm running into in designing this solution are
1) Access strips off the formatting of the Excel spreadsheet when I import/export. I need the formatting of the original source sheet to be retained.
2) Access removes the image that is imbedded in the original Excel spreadsheet when import/exporting.
3) I have no idea how I would automate it so that the user can tell it to run and it automatically opens their 200+ source sheets and updates them.


If anybody can help shed some light onto this problem, I would be most appreciative.


Thanks in advance.




Hi all,

I am new to Access, have used it in high school a few years back and have forgotten most of it (but computer literate and able to follow technical details so seriously-dumbed-down answers aren't required )

To make things clearer, here is my situation explained:

The task:
When staff have in-home appointments, let's say 10 appointments for example, they come back and out of those 10 appointments they secure 5 in-office appointments. These 5 in-office appointments need to be recorded in what we call a 'Run Sheet' for the upcoming appointment date so we know who is coming in on that date.

The current procedure:
Open the Excel Spreadsheet which is basic. Columns such as Name, Date of Appointment, Time of Appointment, Consultant, Finance Position, Notes. I enter the details for the client and save it. Each morning management prints it out for the meeting so as to discuss the upcoming in-office appointment surrounding those clients.


The desired setup:
To create an Access Database for this rather than editing an Excel Spreadsheet each morning (when new people are added I need to insert fields so it is in time order, then I need to cut them and insert elsewhere for cancellations and things get fiddly). I would like to be able to open up a form and enter the details. Example:

Form is 'New Client'
Enter the name, date of appointment, consultant, notes etc
Save the entry

This places the record into a spreadsheet with all other appointment records in the order I have requested through programming it (date; then time order)

This way when I have 10 to add into the spreadsheet, I can simply enter all 10 client records and save them. Then go into the spreadsheet and print it knowing the clients are in order. I also need to be able to edit these records later if the client cancels, so either a drop-down box (combo box I've seen people calling it?) or a tick-box for 'Cancellation' and when I re-save the record it will change the font colour to grey and strike-through the record on the spreadsheet, then also put it at the bottom of the last non-cancelled record)

What I would like to know having posted this here:
Does anyone know of a template that is specific to this task or almost specific, requiring little change? I have searched Office Online, downloaded templates to change, tried to make one from scratch etc. but my code/macro/rules knowledge is not up to scratch.

It would be great if I could come up with something on Access since our work computers all have it rather than asking management to fork out extra $$ for an expensive appointment management program.

Your help is much appreciated!




Good evening all, and happy holidays.

I've spent the last month or so developing a simple MS Access database for my team at work to track our daily tasks. Up to this point, I've always been able to find an answer to my Access questions on this board or others like it, but this time no such luck.

We all open the db in the morning and basically keep it open all day. Most of our daily tasks are completed through excel spreadsheets with their own macros and queries. For my database, I simply have a hyperlink field to store locations on the network. These hyperlinks are displayed on a form, which users click on to open each task sheet. For some reason, the worksheets behave differently when opened from the hyperlinks in MS Access than they do if opened from excel itself.

In particular, the macros in the excel sheet fail at places they should not. For example the excel vba code will have a line referencing

Activeworkbook.name

but we will get a runtime error on that line, with activeworkbook.name not getting picked up. This is especially puzzling because these macros still have to be manually kicked off from the main page of the excel book - so I know the book has the focus. If I close the book and reopen it from excel (and not the access link) the very same macro works fine!

Has anyone seen anything like this before? I will be glad to provide the specific runtime error once I am back in the office tomorrow morning if that may help.

Cheers,
Ben




Hi all,

I am very new to the Access world, and I am hoping to get some help here in how to get started. I have a shared spreadsheet that is saved on our company's server. This spreadsheet houses large amounts of data for individuals. Multiple users (4 to 10) access this spreadsheet simultaneously; they read through the information for each person, then input 2 fields:

(1) Y or N
(2) 1-2 sentences of notes

This spreadsheet has become too large that it has lost functionality within excel. It has become unbearably slow to scroll, sort, or filter, and when they try to save the spreadsheet with their input, it regularly locks them out even though it's shared.

My understanding is that Access is the preferable platform for a spreadsheet of this size (~200 MB). I have never used Access, and right now, it is not intuitive to me.

The basic needs we have are the ability to:

1. filter fields to only show specific data
2. sort fields so that all data is shown in a specific order
3. multiple users to access the same data
4. multiple users to save their input to a central location (meaning that when I open this file, all of the users' input is shown in one place)
5. The ability for me to add new individuals and their data to the file regularly

The excel spreadsheet is set up in this way:

Checker_Decision_Notes_PersonID_FirstName_LastName _Deadline_Notes

Checker: the user's initials are here
Decision: Y/N
Notes: 1-2 sentences
PersonID: the individuals' Person ID - this is a unique identifier of each row
The remaining fields are all information on the Individual (Person ID)

Does this seem like a possible transition to Access? Could Access meet these needs without functioning as slow as excel?

Thank you for any help you can provide.




I found a macro that was developed to automatically search a folder and import spreadsheets to a database, but it was formatted specifically for columns with numbers and such.

My column setup is as follows:

Code: Column Number Column Name Type 1 Cost Center Text 2 Cost Eleme Text 3 Posting Date 4 CO Doc # Text 5 Amount Text 6 User Name Text 7 FI Doc Text 8 Vendor # Text 9 Name Text 10 Info Field Text 11 Doc Header Text Text 12 Line item Text Text 13 Invoice # Text 14 Entry dt Date 15 Inv date Date 16 Invoice Month Date 17 Housekeeping Text I set up the code as follows, but it gives me an error in the INSERT INTO statement part, "Runtime Error 3075: Syntax Error (Missing Operator) in Query Expression 'Cost Eleme'. The error highlights at db.Execute sSQL.

Thanks for your help!

Code: Sub Import() Dim fs Dim fs2 Dim SourceFolder Dim sSourceDir As String Dim CurrFile Rem the Excel Application Dim objExcel Rem the path to the excel file 'Dim excelPath Rem how many worksheets are in the current excel file Dim worksheetCount Dim counter Rem the worksheet we are currently getting data from Dim currentWorkSheet Rem the number of columns in the current worksheet that have data in them Dim usedColumnsCount Rem the number of rows in the current worksheet that have data in them Dim usedRowsCount Dim row Dim column Rem the topmost row in the current worksheet that has data in it Dim top Rem the leftmost row in the current worksheet that has data in it Dim leftct Dim Cells Rem the current row and column of the current worksheet we are reading Dim curCol Dim curRow Rem the value of the current row and column of the current worksheet we are reading Dim word Dim sSQL As String Dim db As Database sSourceDir = "C:Documents and SettingsR356112DesktopHousekeeping2009" Set fs = CreateObject("scripting.filesystemobject") Set SourceFolder = fs.GetFolder("C:Documents and SettingsR356112DesktopHousekeeping2009") Set db = CurrentDb For Each CurrFile In SourceFolder.Files sFileName = CurrFile.Name sExcelFile = sSourceDir & "" & sFileName If InStr(sFileName, ".xlsx") Then Debug.Print "Reading Data from " & sExcelFile Rem Create an invisible version of Excel Set objExcel = CreateObject("Excel.Application") Rem don't display any messages about documents needing to be converted Rem from old Excel file formats objExcel.DisplayAlerts = 0 Rem open the excel document as read-only Rem open (path, confirmconversions, readonly) objExcel.Workbooks.Open sExcelFile, False, True Rem How many worksheets are in this Excel documents worksheetCount = objExcel.Worksheets.Count Debug.Print "We have " & worksheetCount & " worksheets" Rem Loop through each worksheet For counter = 1 To worksheetCount Debug.Print "-----------------------------------------------" Debug.Print "Reading data from worksheet " & counter & vbCrLf Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(counter) Rem how many columns are used in the current worksheet usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count Rem how many rows are used in the current worksheet usedRowsCount = currentWorkSheet.UsedRange.Rows.Count Rem What is the topmost row in the spreadsheet that has data in it top = currentWorkSheet.UsedRange.row Rem What is the leftmost column in the spreadsheet that has data in it leftct = currentWorkSheet.UsedRange.column Set Cells = currentWorkSheet.Cells Rem Loop through each row in the worksheet For row = 0 To (usedRowsCount - 1) Rem Loop through each column in the worksheet curRow = row + top curCol = column + leftct If Cells(curRow, 1) "" Then sSQL = "INSERT INTO Imports (A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q) VALUES (" For column = 0 To usedColumnsCount - 1 Rem only look at rows that are in the "used" range curRow = row + top Rem only look at columns that are in the "used" range curCol = column + leftct Rem get the value/word that is in the cell word = Cells(curRow, curCol).Value Rem display the column on the screen If curCol = 1 Then sSQL = sSQL & "#" & word & "#," Else sSQL = sSQL & word & ", " End If 'Debug.Print "CurRow " & curRow & " CurCol " & curCol & " " & (word) Next sSQL = Left(sSQL, Len(sSQL) - 2) & ")" Debug.Print sSQL db.Execute sSQL End If Next Rem We are done with the current worksheet, release the memory Set currentWorkSheet = Nothing Next objExcel.Workbooks(1).Close objExcel.Quit End If Next Set currentWorkSheet = Nothing Rem We are done with the Excel object, release it from memory Set objExcel = Nothing db.Close End Sub




before I develop further, I need to know if the following is possible, and how complicated it is, as I'm a novice at VBA:

1. Open a specific spreadsheet in a relative path folder (perhaps ask the user for the path?!?)
2. create a new worksheet in that spreadsheet, and name it with the next sequential number (1,2,3,etc.)
3. Copy data from a template sheet and paste it into the newly created sheet
4. Enter specific information from an access query into certain cells on each row for a given record in the query. For instance, results from the "Labor Hrs" field of the query go into column B, etc.

Thanks for any insight,
Gary




Hi All,

I am try to adapt the VBS code below to search for users in an active directory and append an exsisting access 2007 table.

The code as is works but the output is in excel, but I would like to use it with access.

Can someone help with adapting the vbs code into access VBA module and get it to display the results on the screen for selection before appending the required user data into an exsisting table.

Regards,

Pank

Below is the VBS code.

' Bind to RootDSE - this object is used to
' get the default configuration naming context
' e.g. dc=microsoft,dc=co,dc=uk
set objRootDSE = getobject("ldap://RootDSE")

' Search box to search for user name
Name = InputBox("Enter Surname Name to search?")
' File name to export to
strExportFile = "C:user.xlsx"
' Root of search set to default naming context.
' e.g. dc=microsoft,dc=co,dc=uk
' RootDSE saves hard-coding the domain.
' If want to search within an OU rather than the domain,
' specify the distinguished name of the ou. e.g.
' ou=students,dc=microsoft,dc=co,dc=uk"
strRoot = objRootDSE.Get("DefaultNamingContext")
' Filter for user accounts - could be modified to search for specific users,
' such as those with mailboxes, users in a certain department etc.
strfilter = "(&(objectCategory=Person)(objectClass=User)(sn="& Name&"))"
' Attributes to return from the query
strAttributes = "sAMAccountName,givenName,sn," & _
"physicalDeliveryOfficeName," & _
"mail," & _
"title,department," & _
"company," & _
"l"

'Scope of the search. Change to "onelevel" if you didn't want to search child OU's
strScope = "subtree"
set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")
cn.open "Provider=ADsDSOObject;"
cmd.ActiveConnection = cn
cmd.commandtext = ";" & strFilter & ";" & _
strAttributes & ";" & strScope
set rs = cmd.execute
' Use Excel COM automation to open Excel and create an excel workbook
set objExcel = CreateObject("Excel.Application")
set objWB = objExcel.Workbooks.Add
set objSheet = objWB.Worksheets(1)
' Copy Field names to header row of worksheet
For i = 0 To rs.Fields.Count - 1
objSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
objSheet.Cells(1, i + 1).Font.Bold = True
Next
' Copy data to the spreadsheet
objSheet.Range("A2").CopyFromRecordset(rs)
' Save the workbook
objWB.SaveAs(strExportFile)
' Clean up
rs.close
cn.close
set objSheet = Nothing
set objWB = Nothing
objExcel.Quit()
set objExcel = Nothing




Good afternoon all --

I have created a macro that will export fields from Access to a formatted Excel spreadsheet so that it can be printed and mailed to one of our vendors. They are VERY specific about the format of this form.

At any rate, this works when someone is entering data on a dispute form, they can click a Print Dispute button, and the following code is executed to load the info into Excel and print the form:


	Code:
	    Dim app As Excel.Application
    Dim wb As Excel.Workbook
    Dim sht As Excel.Worksheet
    Dim rng As Excel.Range
    Dim cht As Excel.Chart
    
    Dim contactName As String
        
    Set app = CreateObject("Excel.Application")
    app.Visible = True
    Set wb = app.Workbooks.Open("X:DisputeTemplate.xls")
    Set sht = wb.ActiveSheet
    
    If Me.disp_dateRequested.Value  "" Then
        sht.Cells(15, 5) = Me.disp_dateRequested.Value
    End If
    
    If Me.disp_contactName.Value  "" Then
        contactName = DLookup("user_name", "tblUsers", "user_id = " & Me.disp_contactName.Value)
        sht.Cells(18, 5) = contactName
    End If
    
    If Me.disp_contactPhone.Value  "" Then
        sht.Cells(19, 5) = Me.disp_contactPhone.Value
    End If
    
    If Me.disp_contactFax.Value  "" Then
        sht.Cells(20, 5) = Me.disp_contactFax.Value
    End If
    
    If Me.disp_targetDocNum.Value  "" Then
        sht.Cells(15, 11) = Me.disp_targetDocNum.Value
    End If
    
    If Me.disp_amount  "" Then
        sht.Cells(16, 11) = Me.disp_amount.Value
    End If
    
    If Me.disp_storeNum.Value  "" Then
        sht.Cells(19, 9) = Me.disp_storeNum.Value
    End If
    
    If Me.disp_id.Value  "" Then
        sht.Cells(20, 9) = Me.disp_id.Value
    End If
    
    If Me.txtTypeInfo.Value  "" Then
        If Me.cboSelectType.Value = "Credit Rebill" Then
            sht.Cells(20, 10) = "Original SV Invoice #" & Me.txtTypeInfo.Value
        End If
    End If
    
    If Me.cboSelectType.Value  "" Then
        If Me.cboSelectType.Value = "Credit Rebill" Then
            sht.Cells(51, 2) = "X"   'Check off Other Type
            sht.Cells(52, 5) = "___Credit___"  'First Line
            sht.Cells(53, 5) = "___Rebill___"  'Second Line
            sht.Cells(53, 8) = "  " & Me.disp_comments.Value  'Comments
        End If
        
        If Me.cboSelectType.Value = "Duplicate Credits" Then
            sht.Cells(51, 2) = "X"   'Check off Other Type
            sht.Cells(52, 5) = "___Duplicate___"  'First Line
            sht.Cells(53, 5) = "___Credits___"  'Second Line
            sht.Cells(53, 8) = "  " & Me.disp_comments.Value  'Comments
        End If
        
        If Me.cboSelectType.Value = "Unpaid Invoice(s)" Then
            sht.Cells(39, 2) = "X"    'Check off Unpaid Invoice(s) Type
            sht.Cells(42, 6) = "___" & Me.disp_typeInfo.Value & "___"
            sht.Cells(53, 8) = "  " & Me.disp_comments.Value  'Comments
        End If
        
        If Me.cboSelectType.Value = "Other" Then
            sht.Cells(51, 2) = "X"    'Check off Other Type
            sht.Cells(52, 5) = "See Below"
            sht.Cells(56, 8) = "  " & Me.disp_comments.Value  'Comments
        End If
    End If
    
    
    
    'Print Active Workbook
    app.ActiveWorkbook.PrintOut Copies:=1, Collate:=True
    app.Quit
    app.SendKeys ("{TAB} ") 'Do not save changes

Sorry for the long code. What I would like to do is setup a "print queue" of sorts, where each dispute will have a flag that tells whether or not it has been printed at least once. If not, I want this script to be adapted to where it will export the data to excel, put in a page break, then keep adding new "forms" as it goes.

Would I be better off designing the form through an access report and skipping excel altogether?

I didn't think this one out very thoroughly, I'm afraid.

Thanks for any insight!


Page 1 of 2.
Results 1...20 of 33