How can I auto fit the columns in an access table through Excel vba
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
So basically I've created a macro to format a sheet in a certain way and I've got a code which exports and stores this info in an access database.
The thing is that the columns are the default size and not auto fitted. How can I auto fit the columns in access through Excel vba. I've tried googling and searching online but I can only seem to find formats from access to Excel. Not Excel to access. Any help would be appreciated.
Thanks
excel vba excel-vba ms-access
edited Nov 22 '18 at 14:33
Pᴇʜ
25.3k63052
asked Nov 22 '18 at 14:32
xyzabc12341000xyzabc12341000
51
add a comment |
So basically I've created a macro to format a sheet in a certain way and I've got a code which exports and stores this info in an access database.
The thing is that the columns are the default size and not auto fitted. How can I auto fit the columns in access through Excel vba. I've tried googling and searching online but I can only seem to find formats from access to Excel. Not Excel to access. Any help would be appreciated.
Thanks
excel vba excel-vba ms-access
edited Nov 22 '18 at 14:33
Pᴇʜ
25.3k63052
asked Nov 22 '18 at 14:32
xyzabc12341000xyzabc12341000
51
Which columns are you talking about when you say "The thing is that the columns are the default size and not auto fitted." Can you show a screenshot? If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:37
It's not letting me add a picture for some reason, so I'll try and explain better. Do you know when you create a table in access our Excel, the size of the cell is the size that Excel sets it. If your character length is higher than what can fit in the cell, the cell size won't change unless you double click on the top of the column or drag the columns to the size of your own choosing
– xyzabc12341000
Nov 22 '18 at 14:51
As I said: If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:57
add a comment |
So basically I've created a macro to format a sheet in a certain way and I've got a code which exports and stores this info in an access database.
The thing is that the columns are the default size and not auto fitted. How can I auto fit the columns in access through Excel vba. I've tried googling and searching online but I can only seem to find formats from access to Excel. Not Excel to access. Any help would be appreciated.
Thanks
excel vba excel-vba ms-access
edited Nov 22 '18 at 14:33
Pᴇʜ
25.3k63052
asked Nov 22 '18 at 14:32
xyzabc12341000xyzabc12341000
51
So basically I've created a macro to format a sheet in a certain way and I've got a code which exports and stores this info in an access database.
The thing is that the columns are the default size and not auto fitted. How can I auto fit the columns in access through Excel vba. I've tried googling and searching online but I can only seem to find formats from access to Excel. Not Excel to access. Any help would be appreciated.
Thanks
excel vba excel-vba ms-access
excel vba excel-vba ms-access
edited Nov 22 '18 at 14:33
Pᴇʜ
25.3k63052
asked Nov 22 '18 at 14:32
xyzabc12341000xyzabc12341000
51
edited Nov 22 '18 at 14:33
Pᴇʜ
25.3k63052
asked Nov 22 '18 at 14:32
xyzabc12341000xyzabc12341000
51
edited Nov 22 '18 at 14:33
Pᴇʜ
25.3k63052
edited Nov 22 '18 at 14:33
Pᴇʜ
25.3k63052
edited Nov 22 '18 at 14:33
Pᴇʜ
25.3k63052
25.3k63052
asked Nov 22 '18 at 14:32
xyzabc12341000xyzabc12341000
51
asked Nov 22 '18 at 14:32
xyzabc12341000xyzabc12341000
51
asked Nov 22 '18 at 14:32
xyzabc12341000xyzabc12341000
51
51
Which columns are you talking about when you say "The thing is that the columns are the default size and not auto fitted." Can you show a screenshot? If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:37
It's not letting me add a picture for some reason, so I'll try and explain better. Do you know when you create a table in access our Excel, the size of the cell is the size that Excel sets it. If your character length is higher than what can fit in the cell, the cell size won't change unless you double click on the top of the column or drag the columns to the size of your own choosing
– xyzabc12341000
Nov 22 '18 at 14:51
As I said: If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:57
add a comment |
Which columns are you talking about when you say "The thing is that the columns are the default size and not auto fitted." Can you show a screenshot? If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:37
It's not letting me add a picture for some reason, so I'll try and explain better. Do you know when you create a table in access our Excel, the size of the cell is the size that Excel sets it. If your character length is higher than what can fit in the cell, the cell size won't change unless you double click on the top of the column or drag the columns to the size of your own choosing
– xyzabc12341000
Nov 22 '18 at 14:51
As I said: If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:57
Which columns are you talking about when you say "The thing is that the columns are the default size and not auto fitted." Can you show a screenshot? If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:37
Which columns are you talking about when you say "The thing is that the columns are the default size and not auto fitted." Can you show a screenshot? If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:37
It's not letting me add a picture for some reason, so I'll try and explain better. Do you know when you create a table in access our Excel, the size of the cell is the size that Excel sets it. If your character length is higher than what can fit in the cell, the cell size won't change unless you double click on the top of the column or drag the columns to the size of your own choosing
– xyzabc12341000
Nov 22 '18 at 14:51
It's not letting me add a picture for some reason, so I'll try and explain better. Do you know when you create a table in access our Excel, the size of the cell is the size that Excel sets it. If your character length is higher than what can fit in the cell, the cell size won't change unless you double click on the top of the column or drag the columns to the size of your own choosing
– xyzabc12341000
Nov 22 '18 at 14:51
As I said: If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:57
As I said: If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:57
add a comment |
1 Answer
1
active
oldest
votes
Access tables don't support autofit.
You can influence column width in Access through CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth.
However, the common value for autofit, -2, doesn't work for this property, and just sets it to the default width.
That means you have to calculate the width of a certain table column, or copy the width from Excel if you're using identical font settings.
answered Nov 22 '18 at 15:06
Erik AErik A
20.2k62441
So do you write it as: CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth = 1. Or have I done something wrong because when I did this, the column disappeared
– xyzabc12341000
Nov 22 '18 at 15:54
Eh, you've just set the column width to 1 twip, I'd expect it to disappear. -1 = default width, -2 = autofit (doesn't work), and else the width gets interpreted as a value in twips.
– Erik A
Nov 22 '18 at 16:00
Sorry I didn't know the units were in twips. I've done what you've suggested and it works properly. I've been looking for a solution to this for a while, so I appreciate it. Thanks again
– xyzabc12341000
Nov 22 '18 at 16:43
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433171%2fhow-can-i-auto-fit-the-columns-in-an-access-table-through-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Access tables don't support autofit.
You can influence column width in Access through CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth.
However, the common value for autofit, -2, doesn't work for this property, and just sets it to the default width.
That means you have to calculate the width of a certain table column, or copy the width from Excel if you're using identical font settings.
answered Nov 22 '18 at 15:06
Erik AErik A
20.2k62441
So do you write it as: CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth = 1. Or have I done something wrong because when I did this, the column disappeared
– xyzabc12341000
Nov 22 '18 at 15:54
Eh, you've just set the column width to 1 twip, I'd expect it to disappear. -1 = default width, -2 = autofit (doesn't work), and else the width gets interpreted as a value in twips.
– Erik A
Nov 22 '18 at 16:00
Sorry I didn't know the units were in twips. I've done what you've suggested and it works properly. I've been looking for a solution to this for a while, so I appreciate it. Thanks again
– xyzabc12341000
Nov 22 '18 at 16:43
add a comment |
Access tables don't support autofit.
You can influence column width in Access through CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth.
However, the common value for autofit, -2, doesn't work for this property, and just sets it to the default width.
That means you have to calculate the width of a certain table column, or copy the width from Excel if you're using identical font settings.
answered Nov 22 '18 at 15:06
Erik AErik A
20.2k62441
So do you write it as: CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth = 1. Or have I done something wrong because when I did this, the column disappeared
– xyzabc12341000
Nov 22 '18 at 15:54
Eh, you've just set the column width to 1 twip, I'd expect it to disappear. -1 = default width, -2 = autofit (doesn't work), and else the width gets interpreted as a value in twips.
– Erik A
Nov 22 '18 at 16:00
Sorry I didn't know the units were in twips. I've done what you've suggested and it works properly. I've been looking for a solution to this for a while, so I appreciate it. Thanks again
– xyzabc12341000
Nov 22 '18 at 16:43
add a comment |
Access tables don't support autofit.
You can influence column width in Access through CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth.
However, the common value for autofit, -2, doesn't work for this property, and just sets it to the default width.
That means you have to calculate the width of a certain table column, or copy the width from Excel if you're using identical font settings.
answered Nov 22 '18 at 15:06
Erik AErik A
20.2k62441
Access tables don't support autofit.
You can influence column width in Access through CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth.
However, the common value for autofit, -2, doesn't work for this property, and just sets it to the default width.
That means you have to calculate the width of a certain table column, or copy the width from Excel if you're using identical font settings.
answered Nov 22 '18 at 15:06
Erik AErik A
20.2k62441
answered Nov 22 '18 at 15:06
Erik AErik A
20.2k62441
answered Nov 22 '18 at 15:06
Erik AErik A
20.2k62441
answered Nov 22 '18 at 15:06
Erik AErik A
20.2k62441
20.2k62441
So do you write it as: CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth = 1. Or have I done something wrong because when I did this, the column disappeared
– xyzabc12341000
Nov 22 '18 at 15:54
Eh, you've just set the column width to 1 twip, I'd expect it to disappear. -1 = default width, -2 = autofit (doesn't work), and else the width gets interpreted as a value in twips.
– Erik A
Nov 22 '18 at 16:00
Sorry I didn't know the units were in twips. I've done what you've suggested and it works properly. I've been looking for a solution to this for a while, so I appreciate it. Thanks again
– xyzabc12341000
Nov 22 '18 at 16:43
add a comment |
So do you write it as: CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth = 1. Or have I done something wrong because when I did this, the column disappeared
– xyzabc12341000
Nov 22 '18 at 15:54
Eh, you've just set the column width to 1 twip, I'd expect it to disappear. -1 = default width, -2 = autofit (doesn't work), and else the width gets interpreted as a value in twips.
– Erik A
Nov 22 '18 at 16:00
Sorry I didn't know the units were in twips. I've done what you've suggested and it works properly. I've been looking for a solution to this for a while, so I appreciate it. Thanks again
– xyzabc12341000
Nov 22 '18 at 16:43
So do you write it as: CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth = 1. Or have I done something wrong because when I did this, the column disappeared
– xyzabc12341000
Nov 22 '18 at 15:54
So do you write it as: CurrentDb.TableDefs("TableNameOrNumber").Fields("FieldNameOrNumber").Properties!ColumnWidth = 1. Or have I done something wrong because when I did this, the column disappeared
– xyzabc12341000
Nov 22 '18 at 15:54
Eh, you've just set the column width to 1 twip, I'd expect it to disappear. -1 = default width, -2 = autofit (doesn't work), and else the width gets interpreted as a value in twips.
– Erik A
Nov 22 '18 at 16:00
Eh, you've just set the column width to 1 twip, I'd expect it to disappear. -1 = default width, -2 = autofit (doesn't work), and else the width gets interpreted as a value in twips.
– Erik A
Nov 22 '18 at 16:00
Sorry I didn't know the units were in twips. I've done what you've suggested and it works properly. I've been looking for a solution to this for a while, so I appreciate it. Thanks again
– xyzabc12341000
Nov 22 '18 at 16:43
Sorry I didn't know the units were in twips. I've done what you've suggested and it works properly. I've been looking for a solution to this for a while, so I appreciate it. Thanks again
– xyzabc12341000
Nov 22 '18 at 16:43
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433171%2fhow-can-i-auto-fit-the-columns-in-an-access-table-through-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Which columns are you talking about when you say "The thing is that the columns are the default size and not auto fitted." Can you show a screenshot? If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:37
It's not letting me add a picture for some reason, so I'll try and explain better. Do you know when you create a table in access our Excel, the size of the cell is the size that Excel sets it. If your character length is higher than what can fit in the cell, the cell size won't change unless you double click on the top of the column or drag the columns to the size of your own choosing
– xyzabc12341000
Nov 22 '18 at 14:51
As I said: If you are talking about an Access table then there is probably no solution for this.
– Pᴇʜ
Nov 22 '18 at 14:57