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]
13 2148
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
I guess I still to new to understand how the code would work. Right now this is the code I have now: -
Private Sub excel_Click()
-
On Error GoTo Macro1_Err
-
-
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
-
-
GoTo Skip_Err
-
-
Macro1_Err:
-
MsgBox Error$
-
-
Skip_Err:
-
Dim xlswkb As Object
-
-
Set xlswkb = CreateObject("Excel.Application")
-
Set xlswkb = GetObject("L:\~Public\DataBase\Sustaining Engineering\Do not Rmove - Jim A 10-1-07\issues.xls")
-
-
xlswkb.Application.Run "issues.XLS!formatting"
-
-
On Error GoTo test_Err
-
-
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
-
-
test_Exit:
-
Exit Sub
-
-
test_Err:
-
MsgBox Error$
-
Resume test_Exit
-
-
End Sub
-
-
This code points to VB code in an Excel file in a folder, I setup to do the formating. The code is:
-
-
Sub formatting()
-
Cells.Select
-
Selection.Rows.AutoFit
-
Selection.Columns.AutoFit
-
Range("B2").Select
-
ActiveWindow.FreezePanes = True
-
Columns("A:Y").Select
-
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
-
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
-
With Selection.Borders(xlEdgeLeft)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = 1
-
End With
-
With Selection.Borders(xlEdgeTop)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = 1
-
End With
-
With Selection.Borders(xlEdgeBottom)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = 1
-
End With
-
With Selection.Borders(xlEdgeRight)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = 1
-
End With
-
With Selection.Borders(xlInsideVertical)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = 1
-
End With
-
With Selection.Borders(xlInsideHorizontal)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = 1
-
End With
-
Range("A1").Select
-
ActiveWorkbook.Save
-
End Sub
-
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.
I have split this thread from the article. Please continue the conversation in here.
ADMIN
Thank you, I just hope I can get an answer.
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. - Dim appExcel As Excel.Application
-
Dim wbExcel As Excel.Workbook, strFileName As String
-
-
strFileName = "C:\Test\Test.xls"
-
-
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.SaveAs strFileName
-
-
Set wbExcel = Nothing
-
appExcel.Quit
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
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
I think I need a delay in the code, so access can create the file before it tries to open it.
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
I have the delay working, now I just nee to get past the Set wbExcel = appExcel. to point to the opened Excel file.
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: -
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)", strFileName, True, "", 0 'This is what I changed
-
-
Set appExcel = CreateObject("Excel.Application")
-
appExcel.Visible = True 'let's take a peek
-
-
'Error should not appear
-
Set wbExcel = appExcel.Workbooks.Open(strFileName)
THANK YOU!!!! It works great. Now I can delete the Excel File I was using. :)
Brian
THANK YOU!!!! It works great. Now I can delete the Excel File I was using. :)
Brian
You are quite welcome, Brian.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |