Open Excel file from Access form, populating certain fields in the Excel file... (1 Viewer)

[michaeljohannes]

Hi VB coders,

I have a bit of code that opens an Excel template file from an Access form:

Private Sub OpenExcelFile_Click()
    
    
    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("C:\ExcelFile.xltm")
    objXLApp.Application.Visible = True

End Sub

This code works well (as expected).

I'm wondering if there is a way to populate certain fields in this excel spreadsheet based on fields from the form I just left?

For example, I would like the new excel file to have the JobNumber and LastName from the access form I just left, populated in the appropriate fields in the excel document.

I'm a little new to VB code so I apologize if this looks weird, but is it something like:

me.LastName (from Access form) = me.c23 (in Excel spreadsheet)

I have a feeling this isn't that hard, but I'm a bit stuck. I would appreciate any help you might be able to offer

Thank you!

Mike

 

[boblarson]

It would be

objExcelBook.ActiveSheet.Range("C23") = Me.LastName

 

[michaeljohannes]

Excellent! Thanks, Bob

Because the linked file is a template file, when one clicks "save" the user must enter a name for the Excel Workbook. Is there a way that I can make the default save name for this new Excel file the "LastName" from the Access Form? I've never done this before or seen it so I'm not positive it's possible...

Cheers!
Mike

 

[boblarson]

objXLBook.SaveAs "C:\YourFilePath\" & Me.LastName

 

[michaeljohannes]

Hey Bob,

Thanks again for the help.

I'm getting a weird error when I run SaveAs code:

The following features cannot be saved in macro free workbooks:

VB Project

To save a file with these features, click NO, and then choose a macro-enabled file type in the File Type field list.

The other tricky thing is that the code:

objXLBook.SaveAs "C:\JOB_CHECKLIST.xltm" & Me.LastName

Saves with a file name:

c:\JOB_CHECKLIST.xltmJOHANNES (the Me.LastName is added after the file extension)

I was wondering if it would be possible to save the new file as:

c:\JOHANNES.xltm

Thanks for your help thus far!

Mike

 

[rainman89]

you are missing the file location.

objXLBook.SaveAs "C:\Location\" & Me.LastName &".xltm"
i think would work

 

[michaeljohannes]

Hey Rainman89,

Thanks for the speedy reply!

I'm still getting errors...

The idea for this simple checklist is that one could open a form in Access, fire a button that opens a Template checklist in Excel, populate relevant fields in the excel spreadsheet, and force the user to save the new excel spreadsheet in a normal macro enable workbook (using Excel 2007).

The file extensions are strange in 2007.... Well, not strange but new...

To better clarify (maybe there is still a better way to do this):


excel 2007 template file = c:\JOB_CHECKLIST.xltm

example last name = "Johannes"

Forced saved file becomes: c:\Johannes.xlsx



The reason I want the user to be forced to save the new excel file as the person's last name is so that when a user goes back to the access form, there will be a button that finds the excel spreadsheet in a folder identical to the "LastName" field.

Any other thoughts are greatly appreciated!

Thanks,
Mike

 

[boblarson]

I'll have to play with it at home with Office 2007. I know that things have changed with the way Excel documents can either be xlsm, xltm, and xlsx.

 

[michaeljohannes]

Cool, thanks Bob

Mike

 

[michaeljohannes]

I thought I would post all the code I have to this point:

Private Sub CreateCheckList_Click()
    
    
    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("C:\JOB_CHECKLIST.xltm")
    objXLApp.Application.Visible = True

    objXLBook.ActiveSheet.Range("D3") = Me.JobOrderNumber
    objXLBook.ActiveSheet.Range("D2") = Me.LastName
    
    
    objXLBook.SaveAs ("C:\" & Me.LastName & ".xlsm")
    
    
End Sub

The .xlsm and .xltm are macro enabled workbooks and workbook template files respectively.

The only thing that doesn't work is:

objXLBook.SaveAs ("C:\" & Me.LastName & ".xlsm")

It prompts the correct procedure in Excel (to save the file) but gives an error as if Excel2007 is trying to use the "SaveAs" to a NON-macro enabled excel file.


So close!

Thanks,
Mike

 

[JonT]

Hi folks, my first post.

I've had a nightmare trying to do the same thing in Access 2003, so I've writing to a CSV using FSO, writing the table headers in manually

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\" & Format(Now, "mmmyy") & "_" & cboTracking.Value & ".csv", True)
a.WriteLine "RecDate" & "," & "Name" & "," & "RefNo" & "," & "Description" & "," & "Status" & "," & _
"Comment" & "," & "ProcessBy" & "," & "CompDate" & "," & "LinkBy" & "," & "CheckBy" & "," & "CheckDate" & _
"," & "ProcessDays" & "," & "Extract"


Then using a SQL string, writing in the rows.

a.WriteLine rst.Fields("RecDate") & "," & rst.Fields("Name") & "," & _
rst.Fields("RefNo") & "," & rst.Fields("Description") & "," & _
rst.Fields("Status") & "," & rst.Fields("Comment") & "," & _
rst.Fields("ProcessBy") & "," & rst.Fields("CompDate") & "," & rst.Fields("LinkBy") & "," & _
rst.Fields("CheckBy") & "," & rst.Fields("CheckDate") & "," & _
rst.Fields("ProcessDays") & "," & rst.Fields("Extract")

iFld = iFld + 1
rst.MoveNext

Loop
a.Close

There's probably a better way of doing this, anyone have any ideas?

My main problem with writing to Excel was the Excel.Quit line wouldn't kill the process.

Cheers,

Jon

 

[JWrightjr]

How would you use the code above to populate fields across multiple worksheets?

 

[JWrightjr]

I am using the following code to populate excel cells on a worksheet:
Private Sub CreateCheckList_Click()


Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\JOB_CHECKLIST.xltm")
objXLApp.Application.Visible = True

objXLBook.ActiveSheet.Range("D3") = Me.JobOrderNumber
objXLBook.ActiveSheet.Range("D2") = Me.LastName


objXLBook.SaveAs ("C:\" & Me.LastName & ".xlsm")


End Sub

Is there a way to populate cells on multiple worksheets in the same workbook?

 

[Tanya]

Hi Michael
While reading one of the responses to your problem it occured to me that you may be simply missing ".value" from your code.

May I suggest you add this
i.e.
objXLBook.SaveAs ("C:\" & Me.LastName.value & ".xlsm")

Good luck

Cheers
Tanya

 

[ChrisCione]

Thanks to everyone who assisted in this thread. I was looking for the same type of code. It works perfectly (to state the obvious)!!

 

[ChrisCione]

I thought I would post all the code I have to this point:

Private Sub CreateCheckList_Click()
 
 
    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("C:\JOB_CHECKLIST.xltm")
    objXLApp.Application.Visible = True
 
    objXLBook.ActiveSheet.Range("D3") = Me.JobOrderNumber
    objXLBook.ActiveSheet.Range("D2") = Me.LastName
 
 
    objXLBook.SaveAs ("C:\" & Me.LastName & ".xlsm")
 
 
End Sub

The .xlsm and .xltm are macro enabled workbooks and workbook template files respectively.

The only thing that doesn't work is:

objXLBook.SaveAs ("C:\" & Me.LastName & ".xlsm")

It prompts the correct procedure in Excel (to save the file) but gives an error as if Excel2007 is trying to use the "SaveAs" to a NON-macro enabled excel file.


So close!

Thanks,
Mike

Could Select Case work with this? I want to put code behind a command button that will open and populate a specific Excel worksheet based on the combo box selection.

I have this for Word. How would it be written for Excel?

Private Sub CommandOtherDocsGo_Click()
'Download to OtherDocs Word Templates.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn’t open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn’t open, create a new instance of Word.
Set appWord = New Word.Application
End If
Dim strDocFile As String
Select Case Me.OtherDocsCombo
Case "Criteria"
strDocFile = "L:\Bper Spec\Cione\Database Files\Criteria.doc"
Case "Exam File Checklist"
strDocFile = "L:\Bper Spec\Cione\Database Files\Exam File Checklist.doc"
Case "Exam File Label"
strDocFile = "L:\Bper Spec\Cione\Database Files\Exam File Label.doc"
End Select
Set doc = appWord.Documents.Open(strDocFile, , True)
With doc
.FormFields("Classification").Result = Me!Classification
.FormFields("ClassCode").Result = Me!ClassCode
.FormFields("WorkCity").Result = Me!WorkCity
.FormFields("WorkCounty").Result = Me!WorkCounty
.Visible = True
.Activate
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
 
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub

Any help is greatly appreciated.