473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Using Excel Automation to change spreadsheet title

I have a question. Can I use this to change an Excel file? I have a button that exports my records to an Excel spreadsheet, but it is not formatted or looks nice. I didn't know if I could use this to format it after I create it.

[Admin Notes: This thread has been split from the Tips of the week article How to use Excel Functions in Access]
Oct 24 '07 #1
13 2148
ADezii
8,834 Expert 8TB
I have a question. Can I use this to change an Excel file? I have a button that exports my records to an Excel spreadsheet, but it is not formatted or looks nice. I didn't know if I could use this to format it after I create it.
Youo would use this very concept, only expanded, to Format an Excel Spreadsheet once Records have been exported. Read this Tutorial by our own NeoPa:
Application Automation
Oct 25 '07 #2
I guess I still to new to understand how the code would work. Right now this is the code I have now:

Expand|Select|Wrap|Line Numbers
  1. Private Sub excel_Click()
  2.     On Error GoTo Macro1_Err
  3.  
  4.     DoCmd.OutputTo acForm, "Issues lookup", "MicrosoftExcelBiff8(*.xls)", "L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".xls", True, "", 0
  5.  
  6.     GoTo Skip_Err
  7.  
  8. Macro1_Err:
  9.     MsgBox Error$
  10.  
  11. Skip_Err:
  12.     Dim xlswkb As Object
  13.  
  14.     Set xlswkb = CreateObject("Excel.Application")
  15.     Set xlswkb = GetObject("L:\~Public\DataBase\Sustaining Engineering\Do not Rmove - Jim A 10-1-07\issues.xls")
  16.  
  17.     xlswkb.Application.Run "issues.XLS!formatting"
  18.  
  19.     On Error GoTo test_Err
  20.  
  21.     DoCmd.OutputTo acForm, "Issue notes", "MS-DOSText(*.txt)", "L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".txt", True, "", 0
  22.  
  23. test_Exit:
  24.     Exit Sub
  25.  
  26. test_Err:
  27.     MsgBox Error$
  28.     Resume test_Exit
  29.  
  30. End Sub
  31.  
  32. This code points to VB code in an Excel file in a folder, I setup to do the formating.  The code is:
  33.  
  34. Sub formatting()
  35.     Cells.Select
  36.     Selection.Rows.AutoFit
  37.     Selection.Columns.AutoFit
  38.     Range("B2").Select
  39.     ActiveWindow.FreezePanes = True
  40.     Columns("A:Y").Select
  41.     Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  42.     Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  43.     With Selection.Borders(xlEdgeLeft)
  44.         .LineStyle = xlContinuous
  45.         .Weight = xlThin
  46.         .ColorIndex = 1
  47.     End With
  48.     With Selection.Borders(xlEdgeTop)
  49.         .LineStyle = xlContinuous
  50.         .Weight = xlThin
  51.         .ColorIndex = 1
  52.     End With
  53.     With Selection.Borders(xlEdgeBottom)
  54.         .LineStyle = xlContinuous
  55.         .Weight = xlThin
  56.         .ColorIndex = 1
  57.     End With
  58.     With Selection.Borders(xlEdgeRight)
  59.         .LineStyle = xlContinuous
  60.         .Weight = xlThin
  61.         .ColorIndex = 1
  62.     End With
  63.     With Selection.Borders(xlInsideVertical)
  64.         .LineStyle = xlContinuous
  65.         .Weight = xlThin
  66.         .ColorIndex = 1
  67.     End With
  68.     With Selection.Borders(xlInsideHorizontal)
  69.         .LineStyle = xlContinuous
  70.         .Weight = xlThin
  71.         .ColorIndex = 1
  72.     End With
  73.     Range("A1").Select
  74.     ActiveWorkbook.Save
  75. End Sub
  76.  
I would like to do the formating in Access, so I can get rid of the Excel File. I know this is not where I am suppose to post this, but I already asked this question, and could not get the answer I needed.
Oct 25 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
I have split this thread from the article. Please continue the conversation in here.

ADMIN
Oct 25 '07 #4
Thank you, I just hope I can get an answer.
Oct 25 '07 #5
ADezii
8,834 Expert 8TB
Thank you, I just hope I can get an answer.
The answer is really not so simple as you may think it is, but here it goes. I took a subset of your code, and adapted it so that I may test it on my PC. The following code segment will Format an Excel Spreadsheet (C:\Test\Test.xls) completely within the confines of Access and Save it under the same Name via Access Automation Code. I wrote it so that you should have no problem applying the remainder of the Formatting by writing similar code to that illustrated. I think this is what you are looking for, but if it is not, let me know and I'll see what I can do.

P.S. - I sort of threw it together, but it appears fully functional, and I received no Compile or Run Time Errors. Don't forget to substitute your own Path in strFileName.
Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As Excel.Application
  2. Dim wbExcel As Excel.Workbook, strFileName As String
  3.  
  4. strFileName = "C:\Test\Test.xls"
  5.  
  6. Set appExcel = CreateObject("Excel.Application")
  7. appExcel.Visible = True    'let's take a peek
  8.  
  9. Set wbExcel = appExcel.Workbooks.Open(strFileName)
  10.  
  11. With appExcel
  12.   .Sheets("Sheet1").Select
  13.   .Rows.AutoFit
  14.   .Columns.AutoFit
  15. End With
  16.  
  17. With appExcel
  18.   .Range("B2").Select
  19.   .ActiveWindow.FreezePanes = True
  20.   .Columns("A:Y").Select
  21.   .Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  22.   .Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  23. End With
  24.  
  25. With appExcel.Selection.Borders(xlEdgeLeft)
  26.   .LineStyle = xlContinuous
  27.   .Weight = xlThin
  28.   .ColorIndex = 1
  29. End With
  30.  
  31. appExcel.DisplayAlerts = False
  32. wbExcel.SaveAs strFileName
  33.  
  34. Set wbExcel = Nothing
  35. appExcel.Quit
Oct 25 '07 #6
Ok I tryed the code, but I found a problem, I have to have the file to open to place the information. When I replace the file name with the one I output the file to now, I comes up with a syntax error. I need to have the file save with the date and time, to keep the files my engineer makes, seperated. The code I have actually creates the file with the date and time it was created. I tried to modify the code but I keep coming up with a run time error. Here is what I did:

Dim appExcel As excel.Application
Dim wbExcel As excel.Workbook, strFileName As String

DoCmd.OutputTo acForm, "Issues lookup", "MicrosoftExcelBiff8(*.xls)", "L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".xls", True, "", 0 'This is what I changed

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True 'let's take a peek

Set wbExcel = appExcel.Workbooks.Open(strFileName) 'This is a run time error

With appExcel
.Sheets("Sheet1").Select 'This is a run time error if the first was removed
.Rows.AutoFit
.Columns.AutoFit
End With

With appExcel
.Range("B2").Select
.ActiveWindow.FreezePanes = True
.Columns("A:Y").Select
.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
End With

With appExcel.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With

appExcel.DisplayAlerts = False
wbExcel.Save

Set wbExcel = Nothing
appExcel.Quit

The set wbexcel, I wasn't sure what I need to change there. If I remove that, then the run time error stops at .Sheets("Sheet1").Select. I stoped there, and I'm not sure what else I can do. If it is not possible to do I will understand.

Thanks

Brian
Oct 26 '07 #7
I modified it again and tried this, I know I'm getting closer. Let me know if there is something I am missing.

Dim appExcel As excel.Application
Dim wbExcel As excel.Workbook, strFileName As String

strFileName = " L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".xls"

DoCmd.OutputTo acForm, "Issues lookup", "MicrosoftExcelBiff8(*.xls)", "L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".xls", False, "", 0

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True 'let's take a peek


Set wbExcel = appExcel.Workbooks.Open(strFileName)

With appExcel
.Sheets("Sheet1").Select
.Rows.AutoFit
.Columns.AutoFit
End With

With appExcel
.Range("B2").Select
.ActiveWindow.FreezePanes = True
.Columns("A:Y").Select
.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
End With

With appExcel.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With

appExcel.DisplayAlerts = False
wbExcel.Save

Set wbExcel = Nothing
appExcel.Quit

Thanks

Brian
Oct 26 '07 #8
I think I need a delay in the code, so access can create the file before it tries to open it.
Oct 26 '07 #9
I just thought that also if we insert a delay, the seconds would have past and won't open the file. The reason the seconds are included, are in case either more then one person would be exporting and it would increment to another file, without saveing over the one just created. Thi might also be causeing the original problem with it opening in the first place. I am open to any ideas to try to keep it the way I have or a way to change the name. The output comand can open the file, so is there a way to do the formatting in the active workbook? I'll get fooling around with the code.

Thanks

Brian
Oct 26 '07 #10
I have the delay working, now I just nee to get past the Set wbExcel = appExcel. to point to the opened Excel file.
Oct 26 '07 #11
ADezii
8,834 Expert 8TB
Ok I tryed the code, but I found a problem, I have to have the file to open to place the information. When I replace the file name with the one I output the file to now, I comes up with a syntax error. I need to have the file save with the date and time, to keep the files my engineer makes, seperated. The code I have actually creates the file with the date and time it was created. I tried to modify the code but I keep coming up with a run time error. Here is what I did:

Dim appExcel As excel.Application
Dim wbExcel As excel.Workbook, strFileName As String

DoCmd.OutputTo acForm, "Issues lookup", "MicrosoftExcelBiff8(*.xls)", "L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".xls", True, "", 0 'This is what I changed

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True 'let's take a peek

Set wbExcel = appExcel.Workbooks.Open(strFileName) 'This is a run time error

With appExcel
.Sheets("Sheet1").Select 'This is a run time error if the first was removed
.Rows.AutoFit
.Columns.AutoFit
End With

With appExcel
.Range("B2").Select
.ActiveWindow.FreezePanes = True
.Columns("A:Y").Select
.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
End With

With appExcel.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With

appExcel.DisplayAlerts = False
wbExcel.Save

Set wbExcel = Nothing
appExcel.Quit

The set wbexcel, I wasn't sure what I need to change there. If I remove that, then the run time error stops at .Sheets("Sheet1").Select. I stoped there, and I'm not sure what else I can do. If it is not possible to do I will understand.

Thanks

Brian
You were getting the Run Time Error because strFileName was never initialized, and was simply a Zero Length String. Try this logic:
Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As excel.Application
  2. Dim wbExcel As excel.Workbook, strFileName As String
  3.  
  4. strFileName = "L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".xls"
  5.  
  6. DoCmd.OutputTo acForm, "Issues lookup", "MicrosoftExcelBiff8(*.xls)", strFileName, True, "", 0 'This is what I changed
  7.  
  8. Set appExcel = CreateObject("Excel.Application")
  9. appExcel.Visible = True 'let's take a peek
  10.  
  11. 'Error should not appear
  12. Set wbExcel = appExcel.Workbooks.Open(strFileName) 
Oct 26 '07 #12
THANK YOU!!!! It works great. Now I can delete the Excel File I was using. :)

Brian
Oct 26 '07 #13
ADezii
8,834 Expert 8TB
THANK YOU!!!! It works great. Now I can delete the Excel File I was using. :)

Brian
You are quite welcome, Brian.
Oct 26 '07 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Paolo | last post by:
Friends, I need help with some code to export different tables to a single spreadsheet in Excel. My excel file is named REPORT and the spreadsheet is named CLIENTS. I do have the code to export...
0
by: ImraneA | last post by:
Hi there Have a database, where front-end interface allows user to select a ms access database. From there, standard tables are linked. Routine, that creates a spreadsheet, for each table a...
3
by: | last post by:
Hello, I have a routine in my Windows application that exports the contents of a datagrid to Excel. It is modeled closely after the HowTo example on MSDN: http://tinyurl.com/5g2jm. Depending...
6
by: Syvman | last post by:
Here's what I've got: I'm trying to grab some data out of an Excel spreadsheet and bring it into Access. I'm able to do it, but only if the Excel spreadsheet is not opened by any other users. I...
7
by: rdemyan via AccessMonster.com | last post by:
I want to make sure that I'm closing an opened spreadsheet correctly. I've been having some locking up problems. The following code adds a dummy row to the spreadsheet to ensure that that the data...
2
by: madeleine | last post by:
I'm hoping the answer to this is that I'm just doing something silly, but I'm really scratching my head over this one. I'm importing data from multiple workbooks, each workbook has a sheet called...
4
by: Belgarath | last post by:
Hello all, I'm a newbie in Access... And before starting to explore this software, I would like to know if it's possible to automate everyday queries result in Excel . If it's possible can...
8
by: Mel | last post by:
Can anyone tell me why I am getting this error? It bombs on this line of my VB code: Public appExcel As New Excel.Application It works when I test it on my machine but once I release the page...
0
by: Tony Hine | last post by:
Problem for Excel Developers One of the problems facing Excel developers moving into MS Access is actually the apparent similarity between MS Access tables and Excel spreadsheets. MS Access is...
4
by: =?Utf-8?B?THluZXJz?= | last post by:
Hello All, We have a VB.NET application writen using VS 2003. This application apens an excel file from a vendor, reads the data and performs whatever functions it needs. We recently upgraded our...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.